T
TechyTemp
I set up a database with four primary tables: customer, orders, order
detail, shipping and shipping detail. There is an additional table called
'monthly open orders'. Originally the tables were designed with primary
keys: customer id, sales order number and invoice number. When I started
inputting the data I realized that the sales order number and invoice number
could not be primary keys because several items were on each order and the
sales order number needed to be repeated. Also, the invoice number was
repeated as well. Sooo...I took the primary key designation off those fields.
But now, the data isn't flowing properly. Sometimes I have more orders on
the order table than I do in the order detail table and vice versa.
To try and solve this problem I decided to use an order id number and make
that the primary key in the orders, order detail, shipping and shipping
details tables.
Two ways to go on this....first, if I just deleted all data and started over
would the order id number, set as a primary key, keep this problem from
recurring and would the downstream tables be properly populated?
or
Second, if I assign order id numbers to the records in the orders table,
would the order id numbers automatically flow thru to the right records? I
have a gut feeling that they won't but hope I'm wrong.
I have until Dec 1 to have this up and running smoothly and I really need as
much feedback as quickly as possible. If I need to delete the data and start
over, I need to do this Monday or Tuesday to allow enough time.
HELP!
detail, shipping and shipping detail. There is an additional table called
'monthly open orders'. Originally the tables were designed with primary
keys: customer id, sales order number and invoice number. When I started
inputting the data I realized that the sales order number and invoice number
could not be primary keys because several items were on each order and the
sales order number needed to be repeated. Also, the invoice number was
repeated as well. Sooo...I took the primary key designation off those fields.
But now, the data isn't flowing properly. Sometimes I have more orders on
the order table than I do in the order detail table and vice versa.
To try and solve this problem I decided to use an order id number and make
that the primary key in the orders, order detail, shipping and shipping
details tables.
Two ways to go on this....first, if I just deleted all data and started over
would the order id number, set as a primary key, keep this problem from
recurring and would the downstream tables be properly populated?
or
Second, if I assign order id numbers to the records in the orders table,
would the order id numbers automatically flow thru to the right records? I
have a gut feeling that they won't but hope I'm wrong.
I have until Dec 1 to have this up and running smoothly and I really need as
much feedback as quickly as possible. If I need to delete the data and start
over, I need to do this Monday or Tuesday to allow enough time.
HELP!