URGENT! - Please, can anyone help with a FK-Design Question

URGENT! - Please, can anyone help with a FK-Design Question

Postby Zero-G. » Sat 30 Aug 2008 08:41

Hey

I don't know, where to ask either...

So, I have the following problem, which I can't solve.
I have two completly different offers tables.
These two tables should be referenced with one invoicenumber table.

In the offers table, I do have an ID field which is set to autoincrement as primary index.

Here seems to be the big problem. - Because, when I set a foreign key in my invoicenumer table, mySQL deletes the offers of both tables.
So, I thought, I could solve this problem when I add a second related column, called offers_type. - This I set to a default value: - In offers table A set to 1 and in offers table B to 2.
In the invoicenumbers table, I do have also added a field with offers_type. - But this is also not working, when I try to add a field, in this case I get an error.

Plese HELP!!!
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby petrutz » Sat 30 Aug 2008 18:41

Not the right place to ask for sure...

But:

If you want to mix indexes from two or more different tables into one column on a different table that is not going to be possible if you want to make it a FK. Instead what I'd do would be leaving it as a normal column.

Another option would be to re-think the relationship between the orders and invoices as it would be easier if you'd link the invoice id into each of the orders.

The third option I can think about is building a dumb offer table. You link the dumb offer table to your invoice table, then on the dumb offer table you add two more fields, one for the type of the offer and another one for the id of the offer. This third field won't be either a Fk field nor a unique index.

If you need more help mail me at this same user at venaver dot info
petrutz
 
Posts: 5
Joined: Fri 29 Aug 2008 04:43

Postby Zero-G. » Mon 01 Sep 2008 06:56

Hey!
Thanks for your help! – I think, that the version, where you told me, that I set the ID of the invoice table into the offers table, would be the best way. Because there I can create FK’s as well!.

THX
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20


Return to dotConnect for MySQL