Single Primary Key for Multiple Tables?

[lwflip]

I've worked with some Access databases before but now I am trying to design my own database. My questions are on the best way to set up the table structure and relationships for my database. I'd like to have one master table with all of our material ID numbers and material types. Then smaller subset tables that have information relating to only the specific types. Here is the basic structure of what I am thinking.



tblMaterials - MaterialID (Primary Key), MaterialName, MaterialType, Product
tblTypeXInformation - MaterialID (Primary Key), MaterialName, Color, etc --- Subset for only material type X
tblTypeYInformation - MaterialID (Primary Key), MaterialName, Density, etc --- Subset for only material type Y
tblSpecifications - MaterialID (Primary Key), MaterialName, Length, Width, Height --- Subset for when Product == TRUE


With this setup I basically only have one Primary Key since most of my tables are subsets of a larger table. Though from what research I've done I've read having the same Primary Key for multiple tables is bad. Is this the best way to setup what I have so far?
Also, all of the subset tables have the MaterialName field. I was intending on having this field always refer back to tblMaterials for it's value. It is a bit of redundant information. I only have it in here to make looking at the data in table view easier since the Primary Key is not very descriptive. Is there a way to make the MaterialName value in the subset tables auto-populate with the MaterialName from its corresponding entry in tblMaterials?

[CJ_London]

Don't mix primary key with autonumber. Primary keys are usually autonumbers but not always

With regards your table design this may be appropriate - you need to decide whether you need the separate tables or not. But assuming you do then

the MaterialID for tblMaterials would be a PK and of type autonumber
the materialID for the other tables would be of type long (not autonumber) and would be both your primary key and family key - it would link back to the MaterialID in tblMaterials.

This is a perfectly valid data structure since materialID in the other tables would not be duplicated

Alternatively, you can have an autonumber primary key in the other tables called say TypeXPK, etc and then MaterialID would be the Family Key.

MaterialName appears in each table - this does not seem correct, but you know your data. If you were going to use this to link back to tblMaterial then this is wrong - you would do as outlined above.

Rather than using ID as a suffix, use PK or FK for clarity (or PFK for combined primary/family keys)

Autonumber is just a way of uniquely identifying an individual record and should not be used for anything else - do not rely on it incrementing by one and there being no gaps. Do not rely on it indicating the order of input either - particularly if importing data or in a multiuser environment. If you need to know these things, then set up a separate field to fulfil this function (e.g. a timestamp)

[orange]

I recommend you work through this tutorial, then apply what you have learned to your own situation.
You will learn table design and relationships.
Good luck