Question on Database Design Tips

Question

This is not a homework, but new task assign by superior. To create a system for in-house end-users, which auto suggest to different assembly line based on each line's capacity. Users need to maintain each line capacity everymonth, and each line have its own product group running on it with unique prority on each assembly line. When the sales order coming, this system will based on the line planning maintenance below to do auto allocation to each assembly lines based on prority setting.

---

Eg, Product A which is under product group XYZ will be running at L1 and L2.
Sample Data
Period | Line_Number | Product_Subgp | Line_Capacity | Prority
201009 | L1 | XYZ | 2000 | 1
201009 | L1 | ABC | 2000 | 2
201009 | L2 | XYZ | 2000 | 2
201009 | L2 | ZZZ | 2000 | 1

---

Table Structure for Line Header : LinHdr

Line_Hdr_ID | Int(AutoIncrement)
Line_Number | VarChar(10)[Primary Key]
Period | VarChar(6) [Primary Key]
Capacity | Numeric(16)
Special_Capacity| Numeric(16)
Line_Description| VarChar(20)

---

Table Structure for Line Header : LinDtl

Line_Dtl_ID | Int(AutoIncrement)
Line_Hdr_ID | Int [Primary key]
Product_Group | VarChar(10)[Primary Key]
Prority | Numeric(2)

---

Questions:

1. Table LinHdr: Line_Number+Period is Primary Key. As these 2 fields will be act as uniqueness and as a seekkey of the data. Am i correct to set both as primary key?

2. Table LinHdr: Need to include Lin_Hdr_ID as primary key? (LinHID which is autoincrement by SQL, will be invisible to users).

   • The purpose of LinHID is to link to LinDtl, so that no need to include redundant data (i.e. Period+Line_Number) into LinDtl, by using Lin_Hdr_ID to link betw 2 tables.

3. Table LinDtl: Line_Hdr_ID+Product_Group will set as Primary Key, to enforce uniqueness. Is this correct to have this Line_Dtl_ID? Is this field necessary? If Line_Hdr_ID+Product_Group already provide uniqueness, can exclude Line_Dtl_ID?

4. Or, it is better to combine 2 tables above into 1 tables, which did not create relational database?

Hope can have advice from expertise here.

Thanks in advance.

Answer 1

1.1. Line Hdr Id is set to autinc - make that the PK. YOu can search on fields w/o them being PK. If they are unique together, then make index on them - need to make them PK and have to then use it as a FK - long key! plus I personally do not like putting dates into PKs

1. See # 1 above -- and, you answered your own question !

2. If you want a concat/composite PK in InDtl then make it LinHdr_id + Lin Dtl Id - this is the "proper" way of doing an identifying relationship.

3. Do not combine tables this will violate 3rd normal forms.

4. COnsider reading a book: Entity Relationship Modeling by Richard Barker.

Answer 2

I agree with what Joe said (was typing it and he beat me to it).

One thing I would add, is that if the column "Line Description" is something that is unique to the line itself, and not to each line/period combination, then you would want to have a separate "Line" table with the LineNum and the LineDescription columns in it. That way you don't have to insert that description value each month when you put in the new period info. If the description changes every month (or it's possible for it to change only for an individual period), then leave it like it you have it.