linking question

[rainbows]

i am confused as to which is correct because i have been told many different things

please see below

you can see i have linked stocklist MATERIALID to Product detail Material some tell me this is correct some tell me it should be

Material to material which one should it be and why and why not the other

thanks steve

 

[MajP]

Probably Neither. Product Detail likely should not have Material (assume that is a descriptive Name) but a key which I guess is MaterialID.
However, even that looks wrong. I would think StockList has a StocklistID and there is another tblMaterial with information only about the material.
You may need to describe in words what these tables contain and other tables.

 

[arnelgp]

first.. Re-start your pc..

 

[rainbows]

stocklist table

there are 96 products and each product has a Bill of materials attached to it . i select the materail from the stocklist then that populates the stock number , product ID and i enter the qty required for that product

 

[MajP]

You have two different fields with different meaning in two tables with the same name, and that is why you are confused

Material in the stock list table is a Descriptive string field. I assume a name

Material in the Product detail table is numeric field and has different information thanMaterial in the Stock list table. I assume that is the foreign key and should like to MaterialID. That is confusing to anyone looking at this and just a bad design. I would call it MaterialID_FK
And you always link PK to FK

 

[MajP]

To avoid these questions and issues I do personally do the following others may disagree.

1. Never name the primary key the same in different tables. Some people just call it ID in every table. Instead
AuthorID, MaterialID, BookID, CarID .... StockListID
2. The Foreign key (linking field) gets FK added to it. Often people resuse the name and the linking field has the same name as the Primary key.
MaterialID in StockList table and MaterialID in Product Detail
I would do
MaterialID in StockList table and MaterialID_FK in Product Detail

Then when I see in code or a query "MaterialID", I now which table it comes from.
However I also have my PK names match my table name
So I would actually have
StockListID and StockListID_FK

So I can see those field names and code or a query and know a lot. What table they came from, what is the PK, FK and how they should join.

 

[rainbows]

am i not correct in making the material a number field in product details so it can link to the materialid in the stocklist ? then use the combo box to give the materail description

 

[MajP]

Yes, but please rename that field if that is the plan.
In the combobox you select the MaterialID, and Material but hide the first column
column widths: 0;2
and bind the combo to column 1

This also assume that Material is a unique field.

 

[rainbows]

thank you for your help and great advise
steve