is this one table or two tables?

[esilverb]

I have to track purchase orders. I also have to track modifications to the
purchase orders. Each purchase order has a unique number and includes the
date, vendor, description and amount. Each modification has a number which is
the p.o. number plus 1 digit (ex: po# 12-34-1234, mod# 12-34-1234-1). For
each modificaiton I have to track the date, description, and amount.
Is this one table with each p.o. and all the mods or is it two tables with
purchase orders in one table and the modifications in a seperate table?
Thanks for your thoughts on this.

 

[pietlinden]

I have to track purchase orders. I also have to track modifications to the
purchase orders. Each purchase order has a unique number and includes the
date, vendor, description and amount. Each modification has a number which is
the p.o. number plus 1 digit (ex: po# 12-34-1234, mod# 12-34-1234-1). For
each modificaiton I have to track the date, description, and amount.
Is this one table with each p.o. and all the mods or is it two tables with
purchase orders in one table and the modifications in a seperate table?
Thanks for your thoughts on this.

Wait, I don't follow.

You should have tables for the following:
Purchase Order (Header)
(PO Number, CustomerID, Date, etc)

and then
PO_LineItem
(PO Number, ItemID, Quantity, Price...)

then mods would be either against the PO_LineItem (Most likely), not
the whole PO.

 

[scp3030]

i would do it as 2 tables because:

if you do it as one table, you will need an undertermined amount of fields
for the modifications - as i am guessing you probably don't know how many
will have to be added in, and for each modification number you'll also be
wanting the date, the description etc

if you do it as one table and enter a new record for each modification
you'll be duplicating the intital data of vendor, description etc - and i
think the principles of database design really advise against this kind of
duplication.


Soooo, in my humble opinion of course, i would go for one table which has
the initial order with some kind of orderid, and a second table to record all
modifications for that specific order, linked by a foreign key in the
modifications table to the primary key, orderid, in the main orders table.


hope this helps, sorry if it confuses!!!

 

[Ken Sheridan]

Two tables. It’s a question of functional dependency. The first table
PurchaseOrders say, would have a primary key po# and non-key columns which
are functionally dependent on the key, i.e. whose values will always be the
same for each purchase order, just Vendor judging by what you say. The
second table, PurchaseOrderVariations say, will have a composite primary key
of two columns po# and variation#, of which po# will be a foreign key
referencing the key of PurchaseOrders. The non-key columns of this table
will be those which are functionally dependent on its key, i.e. whose values
can vary for each modification, date, description, and amount by the sound of
it (but don't call a column Date, as that's the name of a built in function;
use something like VariationDate).

The important thing to understand is that each purchase order will have at
least one matching row in PurchaseOrderVariations, even where no
modifications have been made to the original spec., which is why I've
suggested calling them 'variations' rather than 'mofications'. This initial
'variation' would have a variation# value of 0, so a modification is a
variation where [variation#] > 0, and the initial purchase order is one where
[variation#] = 0.

Apologies in advance if you post back and I don't reply for a few days.
I'll be away incommunicado until the end of the week.

Ken Sheridan
Stafford, England