Number of records Autonumber

[matt beamish]

I have now got further with the design of a database for recording the construction, maintenance, inspection, licensing and gauging of commercial craft on the English/Welsh inland waterways.

I have a slight concern over the potential number of records in some of the tables, and want to ensure that I have not made an oversight.

In particular the gauging records will certainly exceed 40,000. I have set the PK of this table as an Autonumber.

Although I have designed perhaps 50 databases on and off over the years, in general record numbers have been limited, and I have not had to consider them in the design.

For a table of say 50,000 records, is an incrementing Autonumber PK a sensible way to go? I can see that an Autonumber is a Long Integer and a Long Integer can use 4bytes of memory - but what does this mean for the potential records?

Thanks

Matt

 

[boblarson]

An autonumber is probably one of the best choices for the primary key. But one thing should be said - you should not care what number the autonumber gives you. It should matter not if it is incrementing or random, positive or negative. It is only for the system use in maintaining the relationships between records/tables. They are not suitable for "human consumption" as the old saying goes.

 

[boblarson]

For a table of say 50,000 records, is an incrementing Autonumber PK a sensible way to go? I can see that an Autonumber is a Long Integer and a Long Integer can use 4bytes of memory - but what does this mean for the potential records?

That would translate to only 200Kb of data by the way. So, no big thing.

 

[matt beamish]

Bob,
Thanks very much for that. I am very much in the non meaningful PK camp. This was learnt the hard way, and I wouldn't have it any other way now.

Thanks again

Matt