Which table gets the FK? Is there a clear Parent-Child relationship here?

Question

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?

Answer 1

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.

Answer 2

Like in Part Table,

PartID int PK
PartBase varchar(20) AK
PopularityCode char(20) 

Similarly In ProductLine Table ,PK column should be of INT datatype.

Even if ProductLine table is small,it may be FK in Huge Table . So if 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)

);

So PartID,ProductLineID will be composite primary key.

if Part can exists of its own Product Line then ProductLineID will be Nullable FK.

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 ?

Even if 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.

So 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.