1

I have the following tables:

Part-Product Line relationship

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?

  • 1
    You're contradicting yourself a bit - "when I asked myself if it is possible to a part to exist without a product line, the answer seems to be yes." but later you stated "Since each Part must belong to a ProductLine but cannot belong to more than one". The key question you need to answer - does a Part require an associated ProductLine or not? If yes, then does a Part only link to one ProductLine? – HandyD May 31 at 0:14
  • @HandyD Maybe I'm confusing myself when trying to think about this in terms of a parent-child relationship. Yes, 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? – ahh_real_numbers May 31 at 15:22
0

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.

  • By setting the primary key in the Part table as (PartID,ProductLineID) aren't you possibly introducing partial key dependency? For instance PopularityCode is entirely dependent on PartBase and has nothing to do with ProductLine. – ahh_real_numbers Jun 6 at 20:35
  • PopularityCode was always in the question. And also ProductLine-Part is a many to one relationship (or many to one-and-only-one relationship as I mentioned being unsure of.) But still I don't think this answers my original question about the parent-child relationship of these tables. – ahh_real_numbers Jun 7 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.