Relationships and referential integrity

  • Thread starter Thread starter Paolo
  • Start date Start date
P

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)
 
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.
Click to expand...

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)
 
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.
 
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)
 
Dear Paolo
You may have to articulate your idea a little better.
From what I understand you want the user to enter an attribute (Compo ID,
Name or Service) from the attributes table into the line item table, which
you will later use to determine whether a component or a service was
requested.

First problem I see is with different data-types. IDs are normally number
type. Names & Service type are normally text. You may have some issues with
referential integrity if you try to mix-up different data types.

Also, think about how you want the Attributes table and Components table to
interact. Would you have ComponentIDs entered in the tblAttributes along with
Service & Material Names? My feeling - you dont need the Attributes table at
all. You also need to think why you want Component names and Service to be
entered in the same field as Component ID.

Try to envision the end-product too - how you would want the forms to look
like and how the user would enter information. This would help in designing
better tables and relationships.
 
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)
 
Oops I just realized that adding LineItemType violates field
independence, no? That is a change in LineItemType forces a change in
the optional fields?
 
Back
Top