I have the following tables:
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
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
Customer a false one?
If the business requirement is
a business rule, every part must belong to exactly one product line.
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.
PartID int PK PartBase varchar(20) AK PopularityCode char(20)
ProductLine Table ,
PK column should be of
ProductLine table is small,it may be FK in Huge Table .
ProductLineNumber varchar(20) is Index then it may affect performance.
So it should be ,
ProductLine Table ProductLineID int PK ProductLineNumber varchar(20) AK
ProductLineID should be reference in other columns.
Now come the main question.
Foreign Key can be null-able also.
So if as per your Biz. requirement,
Part cannot exists without Product Line then,
create table Part ( Partid int , PartBase varchar(20) not null, ProductLineID int not null, primary key(PartID,ProductLineID) );
PartID,ProductLineID will be composite primary key.
Part can exists of its own Product Line then
ProductLineID will be
then Main question is ,
How many nullable ProductLineID rows will be there in Part table ? create table Part ( Partid int primary key , PartBase varchar(20) not null, ProductLineID int null, PopularityCode char(20) );
I assume Nullable ProductLineID rows will be very negligible 1% or 2%.
In that case
Create Unique Filtered index,
CREATE UNIQUE INDEX FUX_Part_ProductLine ON dbo.Part (Partid,ProductLineID ) WHERE ProductLineID IS NOT NULL ;
When did you mention anything about
PopularityCode is not mention in question,even if there is no issue of
Partial Dependency,still we need Mapping Table.
So New Design of Part table,
create table Part ( Partid int primary key , PartBase varchar(20) not null, PopularityCode char(20) );
Create Mapping table
create table ProductLine_Part_Mapping ( Partid int not null , ProductLineID int not null, primary key(PartID,ProductLineID) );
It is better to create Mapping table in One-to-Many or Many-to-Many relation.
It help specially when records are in millions.
Partid is Independent or not,this design is OK.
If you mention thing like ,
One Productline may contain how many PartID ?
How many rows will be there in each table ?
These question in help in making Index.