General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>> ...VFP requires n-bytes to store an N(n, d) field in a table.
> I use integers, which use 4 bytes, but can store numbers up to 2e9.
> Am I missing something here?
Yes. The easiest way to understand how much space is required to store a N(n, d) field in a VFP table is to create two tables with a single numeric field, say N(1,0) for the first table and N(8, 0) for the second table. Leave the table empty and check the size of the tables (same size). Add a record in each table and check sizes again. The N(8,0) table takes 7 more bytes of disk space.
>> .. some developers (I am one of them) use GUIDs instead of auto-incrementing
>> sequences for reasons that are beyond the scope of this discussion. These
>> GUIDs actually make joins slower.
> I am interested, if you can give me a short overview (or link).
I will post a link later next week.
> I assume you do this so you have unique keys even when introducing data
> at separate places, that can't be connected to the network in real-time,
> for practical reasons. Is this correct?
Yes. But I use them in other situations also. Most auto-incrementing algorithms written for a VFP backend uses open/close table and table/row locking. Depending on the situation, this may not be desired.
> Surely the GUID will use up more space. 16 bytes, right?
> This would make PKs and FKs larger, and joins slower.
Yes to both.
> Do you use GUIDs for most of your tables?
I normally use GUIDs for tables that are frequently updated, especially if there is a possibility of a user not having access to the database (network or web) at all times.
I normally use natural keys for quasi-static lookup tables, especially if the natural key carries some information that is useful to the users (DOT Code).
I also use sequences (even though the best Oracle DBA I know told me there are some problems with Oracle sequences), and composite natural keys (rarely).
>>The point that Walter, JimN, and I have been trying to make is that you
>> have to evaluate the pros and cons of using surrogate/natural keys for each
>> indiviudual table. Surrogate keys are not the mythical silver bullets.
> As I have been stating, I know there are some disadvantages, but I think
> they are outweighed by the advantages. So I use surrogate keys most of the
> time. It seems you do so, too - at least, part of the time.
My comments were directed to what CraigB refers to as lurkers. I read your other posts in this thread and I already knew you were evaluating a situation before deciding on the primary key. I answered your thread because it gave a bullet list of advantages and I thought the lurkers would appreciate a different point of view.
> I will try to take some time during the next few days to write a summary (for
> Frequently Asked Questions) of the different alternatives, and advantages and
> disadvantages of each approach.
Good luck! I may be able to provide some help towards the end of next week.
I just got news that one of my friend's brother passed away and I will spend the next few days helping her take care of things. Don't expect me online for a week or so.
Daniel
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only