possible to use foreign key more than once in a table? 2

[robFSS]

Ok -

Let's say I've got two tables, Order & CustID.

The CustID table is simple. 2 fields. An ID field, (pk) which is a 6 digit unique ID number, and then the customer name & address (single memo field).

Now the Order table is a little meatier. In addition to other various fields, I have three fields that I want to link back to the CustID table. [ul][li]One field is for the customer's office that issued the order[/li][li]another field is for the customer's office that is administering the order[/li][li]and finally a field to indicate with customer's office is going to pay for the order[/li][/ul]

The thing is that I don't want to make three tables(issue, administer, payment) when each table uses all the same codes. This is silly, I know. For example, code ABC123 could be the Issuing office and the Administering office, but code 987REW is the Payment office. Or all three could be different. Or all three could be the same. But the codes are all in the same "family" (Which I didn't create, and I can't control.)

How do I satisfy all three fields in the Order table using just one CustID table?

Is this clear?

Thanks,
Rob

 

[Crowley16]

add 2 more columns in the order table, and link them to cust_ID...

--------------------
Procrastinate Now!

 

[fneily]

Your CustID table is simply wrong. Study Normalization. Each part of the address and name should be a field. Such as firstname lastname address state zip.
And your database is designed incorrectly.
Maybe look at
The Fundamentals of Relational Database Design

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

 

[lespaul]

while the normalization is an issue, if you have a process where there are three different activities supervised by three different customers, then there's no issue with having a table structured like:
[tt]
OrderID issueCID administerCID paymentCID
PK FK To Cust FK to Cust FK to cust
[/tt]


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[robFSS]

fneily.
Thanks for the tip, I'll brush up or my normalization theory. It's been a while. Since it's elementary for you, perhaps you'd like to enlighten me rather than just beat me down. Can I trouble you to consider my question?

I'd prefer to not apply a full blown address structure to this particular entity at this time. This was a concious decision. In truth I have 3 fields in this table, The ID, The Office/Company/Agency Name, and then the address as a memo. Considering that MS Outlook stores addresses is this fashion, I figure it's ok if I do it as well.

As Crowley16 suggested, I have a field in the order table for each ID use. However, when I try to set the relationships connecting a second ID field from the order table to the CustID table, I get an error.

Regards,
Happy 4th

 

[PHV]

You should have 3 instances of the custID tables ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robFSS]

lespaul -

in your example:

OrderID issueCID administerCID paymentCID
PK FK To Cust FK to Cust FK to cust

are you indicating that I must set the issueCID, aministerCID, and paymentCID fields as key fields in this table for it to work?

Thx for the constructive input.
-r

 

[PHV]

I meant:
You should have 3 instances of the custID tables [!]in the RelationShip window[/!] ...

 

[robFSS]

phv -

ever the mack daddy.

how do you spell mack daddy anyway....?

cheers,
Rob

 

[BobRodes]

<Your CustID table is simply wrong. Study Normalization.
While it is generally more practical to break customer info into separate fields, this has nothing to do with normalization. The database is properly normalized.

<And your database is designed incorrectly.
Well, you could explain how. The OP isn't breaking any rules of database design, although there may be some practical issues to address.

<while the normalization is an issue
How so? Seems to me the OP's model is normalized. I do believe there's a practical issue with putting all the customer info in one field, but again, that has nothing to do with normalization.

Other than that, orlotron, PHV and lespaul have answered your question as I would have; it's entirely appropriate to have the same primary key in one table be multiple foreign keys in another table. The point is that if several instances (records) in one entity (table) play different roles in an entity's relationship with a child entity, then you set things up in the manner they have described to reflect that.

As for putting all of your customer info in one string field, I would still consider breaking them into separate fields. The main reason is that it really doesn't cost you anything to do, and that it gives you more flexibility in how you manipulate your data down the line. For example, what if you decide to put out a mailer in two years' time, and find that you have to create a zip sorted mailing list? Things like this generally keep coming up in my experience. To put it another way, you are much more likely over time to regret NOT doing it than regret doing it.

I would also consider renaming your CustID table to Customers and your Order table to Orders, and adopting that convention in future.

Best Regards,

Bob

 

[robFSS]

Bob -

Thanks for taking the time to comment on this.

You seem to know what you're talking about. So if you think it's better to use normal English titles for my tables, I'll proceed with that convention. Should I call it tblCustomers or just Customers?

I'll consider splitting up the address data. Why do they just lump address data together in MS Outlook?

btw - what is "OP"?

thx again-
-r

 

[PHV]

Original Poster ?

 

[BobRodes]

Sorry I've been incognito for the last week. Yes, OP is original poster. Sorry, we use it all the time in the VB forum.

<Should I call it tblCustomers or just Customers?

I would just say Customers, personally. I use the three character header convention for code variables rather than database tables. But there are others who will disagree.

<Why do they just lump address data together in MS Outlook?
Because they have chosen not to provide any querying flexibility on address data. It is a little more economical to store things this way. I would surmise that the assumption going in was that Outlook is for email, and that that will be the focus, and that addresses and phone numbers are provided as a secondary convenience.

Bob

 

[Lilliabeth]

Considering that MS Outlook stores addresses is this fashion, I figure it's ok if I do it as well.

But MS Outlook doesn't store addresses in that fashion.

<Why do they just lump address data together in MS Outlook?
Because they have chosen not to provide any querying flexibility on address data. It is a little more economical to store things this way. I would surmise that the assumption going in was that Outlook is for email, and that that will be the focus, and that addresses and phone numbers are provided as a secondary convenience.

You are confusing data display for storage.

Open any Address Card. Click on the button that displays the address type (such as "Business"), and you will see that there are seperate fields for Address, City, State/Province, Postal Code, and Country.

It is common for data to be displayed much differently from how it is stored.





Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Lilliabeth]

Far and away, the most valuable piece of advice in this thread is fneily recommending some good reading.

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[r937]

Should I call it tblCustomers or just Customers?

the "tbl" prefix is really quite useless


SQL does not allow you to use a table name where you are supposed to use a column name, and SQL also does not allow you to use a column name where you are supposed to use a table name

so if you are reading and trying to understand someone else's queries (or your own, if you wrote them long enough ago to have forgotten them) you may rest assured that names in the FROM clause are all tables, and names in the SELECT, WHERE, GROUP BY, HAVING, and ORDER BY clauses are all columns

but please do be consistent -- if anyone suggests to you that you need to put the "tbl" prefix on table names, ask them whether they also recommend putting the prefix "col" on column names, and if not, why not


r937.com | rudy.ca

 

[Lilliabeth]

I think this recent thread in the MS Office forum is relevant. You can get an idea of what other people think of data stored like this.

http://www.tek-tips.com/viewthread.cfm?qid=1387851&page=1

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[BobRodes]

<Because they have chosen not to provide any querying flexibility on address data.

I shouldn't have answered that in that way. I don't know outlook very well, and assumed that it lumped all the fields together when orlotron said so.

<It is common for data to be displayed much differently from how it is stored.

Lillabeth's point is a very important one. It's very common to confuse the two.

<Far and away, the most valuable piece of advice ....

Is not. MINE is far and away the most valuable piece of advice. Seriously, the link that fniely provides is pretty much a must read for anyone doing RDB design.

Bob

 

[BobRodes]

<Because they have chosen not to provide any querying flexibility on address data.

I shouldn't have answered that in that way. I don't know outlook very well, and assumed that it lumped all the fields together when orlotron said so.

<It is common for data to be displayed much differently from how it is stored.

Lillabeth's point is a very important one. It's very common to confuse the two.

<Far and away, the most valuable piece of advice ....

The link that fniely provides is pretty much a must read for anyone doing RDB design. Of course, I think my advice is valuable too, maybe even almost as valuable as fniely's.

Bob