Relationships and referential integrity

[Paolo]

Hi all,

I have four tables: one is tlbPurchaseOrder, one is tlbLineItem, one is
tlbAttribute and one is tblComponent.

tblPurchaseOrder has a one to many relationship with tblLineItem.
tlLlineItem has a one to many relationship with tblAttribute and
tlbComponent is currently unrelated as I don't know what to do with it.

*tblPurchaseOrder* *tblLineItem*
*tblAttribute*
fldPurchaseOrderID fldLineItem fldLineItem
fldPurchaseOrderID
fldPurchaseOrderID

fldAttribute

fldAttributeName

*TblComponents*
fldComponentID

(With this design I am avoiding the use of nulls in tblLineItem. I
could have done away with tblAttribute, but then there would be empty
fields where a particular field didn't correspond to a particular line
item)

Now fldAttribute will be restricted to values ComponentID, Name, or
ComponentIDRef. A value of ComponentID or ComponentIDRef will restrict
the user to entering values from look-up field fldComponentID.

So my question is the following...what sort of relationship exists
between tblComponents and tblAttributes. Any?

Thanks in advance,

Paolo

(e-mail address removed)

 

[Paolo]

Argh, my tables got fouled. Here they are again:

*tblPurchaseOrder*
fldPurchaseOrderID

*tblLineItem*
fldLineItem
fldPurchaseOrderID

*tblAttribute*
fldLineItem
fldPurchaseOrderID
fldAttribute
fldAttributeName

*TblComponents*
fldComponentID

Also, I wasn't too clear, here:

Now fldAttribute will be restricted to values ComponentID, Name, or
ComponentIDRef. A value of ComponentID or ComponentIDRef will restrict
the user to entering values from look-up field fldComponentID.

What I meant to say is that "...A value of ComponentID or
ComponentIDRef will restrict the user to entering values from look-up
field fldComponentID into fldAttributeName.

Thanks in advance,

Paolo

(e-mail address removed)

 

[mnature]

Your tables seem a bit jumbled. It would appear that you are designing a
database to track purchase orders. Normally you would start with three
tables, tracking each purchase order, having a table of components, and then
having a table which lists each line item of the purchase order, according to
the components which are being ordered.

tbl_PurchaseOrders
PurchaseOrderID (PK)

tbl_Components
ComponentID (PK)

tbl_LineItems
PurchaseOrderID (PK)
ComponentID (PK)

You have mentioned attributes, but I cannot discern whether these are
attributes of the line item, or of the components, so I don't know how to
link them in.

 

[Paolo]

Thanks again for the help, mnature.

Your table structure is how I first envisioned it, but then I realized
that a line item is going to have different fields filled in depending
on whether it contained a component, a service, or a material. So to
answer your question, the attributes are attributes of the line item.
The three possible combination of attributes are:

1) Component ID

2) Material Name, ComponentID Reference

3) Service Name

So the idea is that a line item may or may not contain the attribute
value of a ComponentID. Thus my puzzelment as to where tbl_components
fits in. I hope I'm making sense.

Thanks so much for your help!

Paolo

(e-mail address removed)

 

[Paolo]

Hello Anand,

Thanks for the response. You understood my babbling perfectly. I
decided to do away with the attributes table altogether as I believe I
am overreaching myslelf. I badly need to purchase a book on database
theory. Anyway, I was thinking about the following table structure:

tbl_PurchaseOrders
PurchaseOrderID (PK)

tbl_Components
ComponentID (PK)

tbl_LineItems
LineItemID(PK)
PurchaseOrderID (PK)
ComponentID
ServiceName
MaterialName
ComponentIDRef

A component will only have the ComponentID field filled in. A service
will only have the ServiceName field filled in and a material will have
the MaterialName and ComponentIDRef fields filled in.

Does this make sense? If it does, should I leave tbl_LineItems as is
with the nulls or create some 1 to 1 relationships:

tbl_LineItems
LineItemID(PK)
PurchaseOrderID (PK)

tbl_ComponentLineItem
LineItemID(PK)
PurchaseOrderID (PK)
ComponentID

tbl_ServiceLineItem
LineItemID(PK)
PurchaseOrderID (PK)
ServiceName

tbl_MaterialLineItem
LineItemID(PK)
PurchaseOrderID (PK)
MaterialName
ComponentIDRef

Or I can do the following. Not create some 1 to 1 relationships,
instead tweek the tbl_LineItems a little bit:

tbl_LineItems
LineItemID(PK)
PurchaseOrderID (PK)
LineItemType
ComponentID
CommodityName
ComponentIDRef

By adding LineItemType field, I can distinguish between a service and a
material allowing me to do away with the MaterialName and ServiceName
fields. Does this make sense?

Any help would be greatly appreciated. You know I need it!

(e-mail address removed)

 

[Paolo]

Oops I just realized that adding LineItemType violates field
independence, no? That is a change in LineItemType forces a change in
the optional fields?