Best, Most Efficient way to Relate Child Tables to Parent Tables

[Kumba1]

I have a table called tblSales and tblSalesParts... You enter the Sales Information in TblSales (Mainly Cust. Info), and you enter all the ordered items in tblSalesParts (detail)... i'm at odd's with another DB Programmer as to the best way to relate, or key the information between to the two tables... He suggests that the proper way would be to reference the OrderNumber field, which is text, to relate the two tables... I think it would be better to relate the two tables via the Primary Key of tblSales... I hope someone out there can spread some light on the situation... BTW, the Order Number is Alphanumeric, which is why it's a text datatype... the table structures are listed below..

tblSales
<FieldName> - <datatype>
------------------------
SalesKey - Primary Key
OrderNumber - Text
SaleDate - Date
Salesman - Text
Status - Text


tblSalesParts
<FieldName> - <datatype>
------------------------
SalesPartsKey - Primary Key
ItemID - Text
qty - Integer
price - currency
Released - Yes/No

 

[danvlas]

It doesn't matter on which fields you relate the tables. What is important is tahat the link works.
The most frequent link is PrimaryKey<-ForeignKey
But it also works like UniqueIndex <- ForeignKey.

Most common practice is to use AutoNumber as primary key and Long Integer as foreign key.

A tip: do NOT use meaningful primary keys. Think of a primary key as being something absolutely invisible to the user. Its only purpose is to uniquely identify each row in a table and allow the developer to link the data. Nothing else...

HTH


Daniel Vlas
Systems Consultant

 

[WildHare]

Jeeeze, Dan - I have to respectfully disagree with you there. A primary key with no meaning is useless. We agree that a primary key uniquely identifies each row. But with a meaningless key, you could have 150 million rows that are exactly alike, except for your meaningless PK..where's the uniqueness there?

Primary Keys should be a MEANINGFUL piece of data about the record which, when known, uniquely identifies ONE and ONLY ONE record, and should not normally be subject to alteration.

Primary keys need not be just ONE field, however, A composite primary key is sometimes called for.

Design decisions often depend in the process through which data is being entered, but I rarely, if ever, have depended on an autonumber to be a primary key. All autonumber does is sequentially number the records as they go in, and can NOT, under any circumstances, ensure that each record is unique.

Respectfully,
Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[danvlas]

Jim, I guess it's my English that played a trick on me.
Of course you're right, the PK is the strongest piece of data in a RDBMS.
The Primary Key, whether simple or composite, has two attributes:
The field(s) must be filled in and their concatenation must be unique.
What I meant was that user should not rely on the meaning of the PK's value. For instance, I encountered a situation where the primary key consisted of the names of the employees. They had their first shock when they hired a guy whose name had already been recorded. And a few months later, another guy with the same name (some John Smith).
I'll take the AutoNumber as another example. As you know, a lot of people rely on the meaning of the AutoNumber and allocate it to invoice numbers, thinking about the continuous sequence it provides. And when the sequence is broken, they blame Access for that...

A PK should be very meaningful to the developer but meaningless for the end user. I'll say it again: End user shouldn't even be aware that something so scary exists in his lovely application.

As for AutoNumber use, I use it frequently as primary key in non critical applications. I achieve the unicity of data in records by using unique indexes on several fields. In this way I have 'speedy' joins in queries by using just one line and unique data in records. The autonumber has in this way no meaning except unique identification of each unique row of data.
And I use unique indexes that ignore nulls, so I can't set them as primary keys.
This manner of work has never failed me so far, so I still consider it OK.

I know that the opinions on the AutoNumber are split and I admit there are pros and cons (however, your theory on AutoNumber can be expanded over any primary key except one created on allll fields of the table). But I really do not want to re-open a debate that has taken place many times in these forums with no result.
I just want to tell you that you are right about the primary key use and creation and I wished to say practically the same thing, but chose an unfortunate expression.

I'll go to get a new English handbook, as I can see I'm getting rusty...





Daniel Vlas
Systems Consultant

 

[WildHare]

No problem - your English is still better than my (pick a language.)

Yes, this debate can be pounded into the ground without any resolution.

Your expansion is well taken, and completely credible.

See ya around the forums.. Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Kumba1]

Here's what I ended up doing based on your inputs...

tblSales
<FieldName> - <datatype>
------------------------
SalesKey - Primary Key
OrderNumber - Text (Unique Index)
SaleDate - Date
Salesman - Text
Status - Text


tblSalesParts
<FieldName> - <datatype>
------------------------
SalesPartsKey - Primary Key
ItemID - Text
qty - Integer
price - currency
Released - Yes/No
tblSalesKey - Integer (Full Integrity to tblSales.SalesKey)



Seem like a good, solid, robust foundation?