I have the following tables:
Each ProductLine will encompass many Parts. For this reason, it initially seemed to me that the Part table is a child of the ProductLine table. However, a part is not simply an extension of a product line, and when I asked myself if it is possible to a part to exist without a product line, the answer seems to be yes.
So I'm wondering if the above design appears to be correct or if there is a better design. In the Part table, PartBase can be thought of as the unique identifier for each part (the part number if you will.)
As a somewhat unrelated question, I have these tables set up as a one to many relationship. Since each Part must belong to a ProductLine but cannot belong to more than one, is it more correct to have this as a one-and-only-one to many relationship? Is the only difference that a Part must contain a not null ProductLineNumber in the actual database? Is that different from requiring that ProductLineNumber be not null?
To follow up to @HandyD's comment, as a business rule, every part must belong to exactly one product line. However, when I was thinking about a part as a physical object, it shouldn't need a product line to exist (a product line is just a label after all.) I'm comparing here to how a sale needs a customer to make sense, so that a Sale table would more clearly be the child of a Customer table. Is this distinction between Part–ProductLine and Sale–Customer a false one?
Best Answer
If the business requirement is
So if the rule is, each part belongs to one product line the other side of that relationship is each product line contains zero-to-many parts which should convince you this is a clear instance of a parent/child relationship.
How a something exists in the real world versus how it exists within a business context/database are not always strongly tied. If I have a part on my desk I do not care about it's product line. But if I'm a business and I am selling the part I will probably need to categorize that part for search/reporting/sales/commission purposes.
The other columns you will probably need FKs on (PartType, Grade, Family, PopularityCode, UnitClass) will have the same physical implementation (parent table, foreign key reference) even if the context might not be the same (Each Part is described by One Part Type/PartType describes zero-to-many Parts, etc).
Won't get into the column types or other aspects of the design because that's not pertinent to the question but I'll be happy to provide guidance elsewhere.