Hi,
I would like to hear from you what would be the best way to design
tables with a conditional relationship between them.
I am callin this a conditional relationship, because the external link
can be to one table or to another, but not for both at the same time,
in the same record.
Here is the example.
Table "people" has data about a person, such as name and birthday.
Table "business" has data about a business (office, company).
Both have an (int) id field.
Table "customer" is what really matters. It has some fields about
the client's internal data.
This table will have a "type" field which points to either "people" or
"business". Depending on what kinf of client this is.
This is the problem. An customer can be either a person (from
"people" table) or a business (from "business" table).
So here is some ideas to tie the tables:
1 -
A field "client" which is the id of the client in either the table
"people" or "busisness".
In this situation both "people" and "business" tables have an
"id" with "auto_increment".
2 -
Almost like 1, but making the "id" field from both table being
unique between them.
So if there is an "id" '12' in "people", there will be no '12' in
"business"'s "id".
One way to do it would be to have an special table with an
"auto_increment" field that could be generated in order to use as
the "id" in the table "people" or "business".
So, before inserting a new recording in those tables, you insert a
row in this special table, just to get the auto_incremented number.
This number would be used as the id in those tables and this would
avoid to have duplicated "id"s between the tables (and no need for
locking).
3 -
Instead of having a field "client" like those examples, it would
have two fields: "people" and "business". Each one is a external
link to its corresponding table.
The caveat in this situation is that only one of this fields
should have valid information. This would be specified by the field
"type".
The first example is the simplest to implement if I am doing the
programing (in Perl for example).
However, I am not sure how I would do this with a 3rd party application
such as access.
The second example is the first with a way to avoid repeated 'id's, so
that if for some reason it points to the wrong table, there will bo no
key with the id it is looking for.
Probably a silly solution.
The third solution is probably more easy to implement the relationship,
however I would still have to say which one should get the data based of
customer.type field.
So, for this situation, which solution is the best one, or what type of
solution is most commonly used?
Sure I could choose whatever I feel like when I am programing the
interface with perl, php, or whatever I use.
However, I am looking for a design that would also work on 3rd party SQL
applications like Access, OpenOffice and Crystal Reports without much
trouble.
So, what are your thoughts?
- Raul Dias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
