These days every popular commercial database product is based on the relational database model, first conceived by Ted Codd of IBM way back in 1969. It's remarkable how it has stood the test of time. But the onus is still on the database designer to produce a set of table designs which get the best from this model in terms of flexibilty, maintainability, ease of querying etc.
If you are designing your own database and you are not familiar with these concepts then here is a good article to get you on your way:
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
With regard to one point in the article...
"Counter columns in Microsoft Access make good primary keys, especially when you're having trouble coming up with good candidate keys"
... I would be inclined to always do it this way. So the primary key on any Access table would always just be a simple Autonumber field. That makes things so much simpler when it comes to joing tables in queries. Any other candidates that you might have for primary keys should be set up as a separate unique key.
The information is provided 'as is'. It has worked for us,
but it may not work for you, so you use it at your own risk. We can't
enter into any correspondance about anything recorded here but please feel free to leave a comment.
Geographical coverage: AlphaOne Computing Services
provides a range of small business computer support and I.T. services throughout Sussex, including
Brighton, Hove, Shoreham, Worthing, Steyning, Burgess Hill,
Haywards Heath, Crawley, Lewes, Newhaven, Seaford, Uckfield and Eastbourne.
