(My posts don't seem to be getting through from my newsreader, so this is
from the website - third time lucky?)
Getting your tables and relationships right is absolutely the most important
step. I think you need to grasp two things: how to spot when you should be
creating a relationship, and what the mechanism is for doing this.
Relationships between what, though? You have to identify the "entities" (to
use less technical language: "thingummies"). An entity is something which
exists in its own right, like a valve, an assembly, a customer, a computer.
It'll have properties (represented by fields which can contain values) and it
will have instances: you may have fifty customers in your Customers table -
that's fifty instances of a Customer. It can take practice to be able to say
with confidence that some value (a measurement, cost, colour, whatver) is a
property of this entity rather than that entity.
Then you look for relationships in real life. Think first of the most
common relationship: "one-to-many". One customer may place many orders. One
Country may have many counties. One army may have many divisions. To
represent a one-to-many relationship the table in which you store records for
the "many" side should have a field (a "foreign key") which stores a
reference (a unique ID, - the "key") of the relevant record in the "One"
side. Example: the Orders table in a sales database will contain a field in
which the Customer-ID is stored, showing that a particular order was placed
by customer number xyz. If you want details of that customer, you now know
where in the Customers table to find them.
The other common relationship is "many-to-many". Your application may well
have a few of these. If you have an Assembly, that will consist of many
Parts, but it may not be one-to-many, as a given Part may be used in many
different Assemblies. You use a separate table (an "associative" or
"junction" table) to represent this. Both the Assembly table and the Part
table have a field which is the unique ID ("Primary Key") of the Assembly or
Part. Your "associative" table might have three fields, one for the
Assembly-ID, one for the Part-ID, and one (say) to record the quantity of
Parts for the Assembly instance concerned, so that a particular Pump may need
32 of a particular type of screw. So for a given Assembly, if you want to
know all the parts which go into it, look in the "associative" table (which
might be called Assembly-Part-tbl) for all the records matching a particular
Assembly-ID, and read off the Part-IDs in those records. You'll use queries
to return matching results from all the tables by "joining" them - that's the
process of limiting results to those where keys match across tables.
Separating your tables correctly makes all this surprisingly easy once the
penny drops, but if you get it wrong you'll be in trouble.
I hope that helps. (Let us know!)
You may find these favourite references useful too:
Free samples from Lynda.com on table design:
http://www.lynda.com/home/Player.aspx?lpk4=31001 (A2007)
http://www.lynda.com/home/Player.aspx?lpk4=13150 (A2003)
Microsoft tutorial on table design:
http://tinyurl.com/ms-table-design-tutorial
Phil, London
