Table Design Question

[Scott J.]

I have 2 tables:
tblOrderDetails - ID (PK), OrderNumber, etc.
tblPersonnel - OrderNumber (PK), Initials

The OrderNumber are linked together. This allows the Order to be linked to
the correct personnel. Each table comes from separate imported data. It works
fine except...

1) Order Numbers are reused. I get around this by using a temp table to
import the data and then use an append Unique Records query to tblPersonnel.
- There are multiple OrderNumbers, therefore in the tblOrderDetails table,
but that is OK.

2) The real problem I see going forward is if the personnel changes.
- Say OrderNumber 1 was linked to Bob. Now Judy takes Bob's place and reuses
OrderNumber 1. Then I have OrderNumber 1 linked to two people, but the append
query will fail since the OrderNumber is a PK.

I could set both the OrderNumber and Initials fields as PK's. But the link
to tblOrderDetails won't work.

Do I have to add an Initials field to tblOrderDetails and forget the
tblPersonnel?

Thanks for the suggestions!

Scott J.

 

[Cheese_whiz]

Your table design is both confusing and, I believe, flawed.

It's flawed because you can't call "ordernumber" a "pk" (primary key) AND
say it gets "reused".

It's confusing because I don't understand how "ordernumber" is part of the
personnel table anyway. I've never seen anything like that before.

Hope this helps, or at least makes some sense.

CW

 

[Cheese_whiz]

Scott,

Just to expand a little, normally I've seen people talking about those order
applications and what they have is a personnel table with empolyeeID (pk),
employee name, phone number, address, etc etc. Another table might be
orders, with an orderID (or orderNumber if you prefer) (pk), employeeID (fk
from the Personnel table), orderTotal, customer name (or maybe customerID if
they have a customer table somewhere), etc etc. Usually, there's a third
table called "Order Details", and that might take a summary item from the
order table and break it down providing description of it, cost per unit,
etc.

I could be wrong about the third table, but the important thing is that you
can't reuse the pk of any table, and its extremely confusing to have
someething called "orderNumber" as the pk of your personnel table, imo.

HTH,
CW

 

[Scott J.]

Thanks for the comments.

The OrderNumber is the PK only in the Personnel table (This is perhaps a
confusing table name for you. My database actually uses tblShipData.). And it
is only the PK AFTER the append query filters out duplicates (from a temp
table). Therefore, the OrderNumber will never be duplicated in tblShipData
(tblPersonnel), but it will as the FK in tblOrderDetails.

Perhaps the difference in this case is that I'm importing data from another
database through *.prn files. (Really limits the table structure!) One prn
file has the order information (minus the employee), the other has the
employee. Order #'s are indeed reused (much to my chagrin). I'm certain the
original database has a unique key of it's own, but it is not available from
the prn files.

I do have additional tables linking in employee details but I see that as
outside the issue. Hopefully I didn't confuse the situation even more.

Thanks again!
Scott J.

 

[Cheese_whiz]

Scott,

Glad you cleared that up <G>

Seriously, there's always a bit of a risk when someone like me tries to
"give back" for all the help he's received. Maybe this is one of those
times. Maybe the situation is a bit over my head (wouldn't have to be
particularly high to achieve that milestone).

Hopefully someone else can jump in and help you out. My brain has trouble
understanding anything when it's fundamental assumptions about a particular
topic are shaken. I may forget I even saw this thread <G>.

Good luck!,
CW

 

[Scott J.]

Thanks for the help all the same!

Scott J.

Scott,

Glad you cleared that up <G>

Seriously, there's always a bit of a risk when someone like me tries to
"give back" for all the help he's received. Maybe this is one of those
times. Maybe the situation is a bit over my head (wouldn't have to be
particularly high to achieve that milestone).

Hopefully someone else can jump in and help you out. My brain has trouble
understanding anything when it's fundamental assumptions about a particular
topic are shaken. I may forget I even saw this thread <G>.

Good luck!,
CW

 

[Tim Ferguson]

I have 2 tables: tblOrderDetails - ID (PK), OrderNumber, etc.
tblPersonnel - OrderNumber (PK), Initials

I don't really understand how this is meant to work either. If
tblPersonnel is a table of records about people, why are they identified
by an order number? If they are records about orders, how come they have
Initials and why go out of your way to confuse us by calling Personnel?

1) Order Numbers are reused. I get around this by using a temp table
to import the data and then use an append Unique Records query to
tblPersonnel. - There are multiple OrderNumbers, therefore in the
tblOrderDetails table, but that is OK.

No it's not okay. Step back for a minute - in Real Life how do you identify
a particular Order? If the OrderNumbers are re-used, then presumably you
have some other method of distinguishing them. Whatever that is is probably
the basis for the PK of the orders table.

2) The real problem I see going forward is if the personnel changes.
- Say OrderNumber 1 was linked to Bob. Now Judy takes Bob's place and
reuses OrderNumber 1. Then I have OrderNumber 1 linked to two people,
but the append query will fail since the OrderNumber is a PK.

But if they are all in one table, OrderNumber 1 is not linked to Bob, it
_is_ Bob. If the person changes, you change the Intitials attribute of the
particular record.

Or you do the thing right: people and orders are clearly separate things
and deserve their own tables. You also need to clarify how these two things
relate to each other. Will Judy and Bob jointly own OrderNumber 1? Can Judy
or Bob ever own another ordernumber? Can you have people who do not own an
order? Can you have orders that don't have owners? etc etc.

[from a later post:]

Perhaps the difference in this case is that I'm importing data from
another database through *.prn files. (Really limits the table
structure!)

No it doesn't. It might give you a bit of a headache in parsing and
cleaning the input data, but you are not doing yourself a favour by
crippling the data model. Get the schema right and everything will flow
correctly from that -- skip the design stage and you will really pay for it
later in much more than time.

Best of luck


Tim F

 

[Scott J.]

Tim, thanks for the comments. I only now just noticed that you had replied.

I think you are right regarding the method of importing the data. Even
though I'm importing from two sources (which made me think of two tables),
I'm going to keep it all in one table. Everything is relating back to the
Order #, so I still believe it is normalized. Thanks for the help!

Scott J.

I have 2 tables: tblOrderDetails - ID (PK), OrderNumber, etc.
tblPersonnel - OrderNumber (PK), Initials

I don't really understand how this is meant to work either. If
tblPersonnel is a table of records about people, why are they identified
by an order number? If they are records about orders, how come they have
Initials and why go out of your way to confuse us by calling Personnel?

1) Order Numbers are reused. I get around this by using a temp table
to import the data and then use an append Unique Records query to
tblPersonnel. - There are multiple OrderNumbers, therefore in the
tblOrderDetails table, but that is OK.

No it's not okay. Step back for a minute - in Real Life how do you identify
a particular Order? If the OrderNumbers are re-used, then presumably you
have some other method of distinguishing them. Whatever that is is probably
the basis for the PK of the orders table.

2) The real problem I see going forward is if the personnel changes.
- Say OrderNumber 1 was linked to Bob. Now Judy takes Bob's place and
reuses OrderNumber 1. Then I have OrderNumber 1 linked to two people,
but the append query will fail since the OrderNumber is a PK.

But if they are all in one table, OrderNumber 1 is not linked to Bob, it
_is_ Bob. If the person changes, you change the Intitials attribute of the
particular record.

Or you do the thing right: people and orders are clearly separate things
and deserve their own tables. You also need to clarify how these two things
relate to each other. Will Judy and Bob jointly own OrderNumber 1? Can Judy
or Bob ever own another ordernumber? Can you have people who do not own an
order? Can you have orders that don't have owners? etc etc.

[from a later post:]

Perhaps the difference in this case is that I'm importing data from
another database through *.prn files. (Really limits the table
structure!)

No it doesn't. It might give you a bit of a headache in parsing and
cleaning the input data, but you are not doing yourself a favour by
crippling the data model. Get the schema right and everything will flow
correctly from that -- skip the design stage and you will really pay for it
later in much more than time.

Best of luck


Tim F