View Single Post
Old 11-15-2019, 12:07 PM   #15
Banishment Pending
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design & Relationships

PRS11-123456-UID actually represents only one data point, not three.
Semantics is getting in the way, your just not thinking general enough. When you put a value in the field [PRS11-123456-UID] that is one data point, but it represents 4 pieces of data: PRS11 is data, 123456 is data, UID is data and the value you've placed in the field is data. That is wrong. Those 3 pieces of data should not be in the field name, they too should be values in a field. See my sales by year example.

So the 60cm product line consists of approx. 20 components and 25 points of data to track.
This data should be accomodated in another table, and the amount of components and points of data is irrelevant in the structure of your database.

comp_ID, autonumber, primary key
ID_ProductLine, number, foreign key to Product line table
comp_Type, string, type of component--it sounds like you want to jam this in a field name
comp_Value, string, this is holds the value you are putting in your table currently

That's it, those field accomodate an unlimited amount of components and product lines. Instead of 20 fields for 20 components, you insert 20 records into this table for them.
plog is offline   Reply With Quote