I'm about to start working on a new project and I would like to have
some ideas from you guys. I already got an idea how to do it, but I'm
not sure how good it is.
This is the problem:
We have about 20 tables with data. We have Persons, Addresses, Vehicles,
Properties, Phones...
The problem that we have is they want to have everything related (many
to many) For example, we may have a Person related with an address, or a
Vehicle with an address, a property with 2 persons... any kind of
combination that you may think.
How to design that? First I thought in having 20 tables and creating one
link table between every two types. As you can see, that wouldn't be a
good solution, because we would have too many of these intermediate tables.
Then I thought that maybe I could have only 1 table with the data of the
20 types and have an extra field that tell us what type it is (if it is
a Person, an Address, ... )
The table would be like this:
Table A
--
PK (autoincrement)
RecordType (from 1 to 20)
Custom1
Custom2
Custom3
Custom4
...
Once I have that, I would add an extra table that give us the relations
between 2 records of Table A. Something Like this:
Table B
--
FK_1 (Foreign key to a record in Table A)
FK_2 (Foreign key to a record in Table A)
What do you guys think? I know it's not really nice but I cannot find a
better solution for that.
Thanks a lot!No - do not have one table for everything - this is very bad. Might as
well use text files to store your data, because you wouldn't need a
relational database.
The correct solution is your first instinct : linking tables that link
one entity to one other entity.
I suspect that everything doesn't truly relate to everything else in a
many-many relation. (e.g., Addresses-Properties).
Once you determine the actual many-many relations, why would having it
done right result in "too many tables?" [sounds like the emporer in
"Amadeus" telling Mozart his music has "too many notes"]
Star wrote:
> Hi,
> I'm about to start working on a new project and I would like to have
> some ideas from you guys. I already got an idea how to do it, but I'm
> not sure how good it is.
> This is the problem:
> We have about 20 tables with data. We have Persons, Addresses, Vehicles,
> Properties, Phones...
> The problem that we have is they want to have everything related (many
> to many) For example, we may have a Person related with an address, or a
> Vehicle with an address, a property with 2 persons... any kind of
> combination that you may think.
> How to design that? First I thought in having 20 tables and creating one
> link table between every two types. As you can see, that wouldn't be a
> good solution, because we would have too many of these intermediate tables
.
> Then I thought that maybe I could have only 1 table with the data of the
> 20 types and have an extra field that tell us what type it is (if it is
> a Person, an Address, ... )
> The table would be like this:
> Table A
> --
> PK (autoincrement)
> RecordType (from 1 to 20)
> Custom1
> Custom2
> Custom3
> Custom4
> ...
> Once I have that, I would add an extra table that give us the relations
> between 2 records of Table A. Something Like this:
> Table B
> --
> FK_1 (Foreign key to a record in Table A)
> FK_2 (Foreign key to a record in Table A)
>
> What do you guys think? I know it's not really nice but I cannot find a
> better solution for that.
> Thanks a lot!
>|||Star wrote:
> Hi,
> I'm about to start working on a new project and I would like to have
> some ideas from you guys. I already got an idea how to do it, but I'm
> not sure how good it is.
> This is the problem:
> We have about 20 tables with data. We have Persons, Addresses, Vehicles,
> Properties, Phones...
> The problem that we have is they want to have everything related (many
> to many) For example, we may have a Person related with an address, or a
> Vehicle with an address, a property with 2 persons... any kind of
> combination that you may think.
> How to design that? First I thought in having 20 tables and creating one
> link table between every two types. As you can see, that wouldn't be a
> good solution, because we would have too many of these intermediate tables
.
>
Not too many. Just as many as are needed - no more and no less. I
suspect it isn't as complicated as "they" think Probably the customer
is just being lazy about the specs - which is normal - but it's the
developer / analyst or PM's job to deal with that and to elicit proper
specs from the users or sponsors.
Take a look at:
http://www.intelligententerprise.com/010101/celko.jhtml
David Portas
SQL Server MVP
--|||"Star" <star@.nospam.com> wrote in message
news:%23RPA0n29FHA.2816@.tk2msftngp13.phx.gbl...
> Hi,
> I'm about to start working on a new project and I would like to have some
> ideas from you guys. I already got an idea how to do it, but I'm not sure
> how good it is.
> This is the problem:
> We have about 20 tables with data. We have Persons, Addresses, Vehicles,
> Properties, Phones...
> The problem that we have is they want to have everything related (many to
> many) For example, we may have a Person related with an address, or a
> Vehicle with an address, a property with 2 persons... any kind of
> combination that you may think.
> How to design that? First I thought in having 20 tables and creating one
> link table between every two types. As you can see, that wouldn't be a
> good solution, because we would have too many of these intermediate
> tables.
> Then I thought that maybe I could have only 1 table with the data of the
> 20 types and have an extra field that tell us what type it is (if it is a
> Person, an Address, ... )
> The table would be like this:
> Table A
> --
> PK (autoincrement)
> RecordType (from 1 to 20)
> Custom1
> Custom2
> Custom3
> Custom4
> ...
> Once I have that, I would add an extra table that give us the relations
> between 2 records of Table A. Something Like this:
> Table B
> --
> FK_1 (Foreign key to a record in Table A)
> FK_2 (Foreign key to a record in Table A)
>
> What do you guys think? I know it's not really nice but I cannot find a
> better solution for that.
> Thanks a lot!
I agree with Trey on this one.
One thing that you mentionned "or a Vehicle with an address".
It sounds silly to me asking "what's the address of the vehicule".
The vehicle should be linked to a person.
(I'm not sure if the vehicle can be owned or registered by more than one
person.)
Once you have the owner, it's easy to find the address or addresses.|||Hey folks,
Thanks for you answers.
> It sounds silly to me asking "what's the address of the vehicule".
Unfortunately not on this system. This systems tracks all kind of
information. Let's say that for example there was a robbery and the
vehicle was found at a certain location. That's why we may have all kind
of weird relationships.
I agree with you guys in having a relationship table for each pair that
we may think that are going to be related, but as I said our users may
have at any moment the need of adding a relation between two very
different kind of data.
If we have one relationship table for each pair, we would have 380
(19*20) relationship tables, and I don't think that would be a good idea.
I will try to convince my supervisors of only having the relationships
that we really know that are going to exist (that's what I have always
wanted), but that's not going to be easy.
By the way, I thought about another solution. Not very good either, but
I would like to have your opinion.
Here it is:
We do have the 20 tables. Persons, Addresses, ...
Each one of these tables look like this:
Table X
--
PK (autoincrement)
[Rest of the fields]
We assign an ID to each one of these tables. For example, for Persons is
1, for Addresses is 2 and so on
Now we have only 1 relationship table and it looks like this:
Table Relations
--
SourceID
FK_Source
DestinationID
FK_Dest
[other fields]
The PK of this table is (SourceID,FK_Source,DestinationID,FK_Des
t)
SourceID and DestinationID are going to have the ID of one of the 20
tables respectively. FK_Source and FK_Dest are the PKs of one of the 20
tables respectively
So if we have in that Relations table something like (1,32123,2,543543)
We know that we have a relation between a person and a address. The PK
of this person is 32123 and the PK of the address is 543543.
The relation between 2 tables changes depending on the type of data.
Not really a relational database...
What do you guys think?
Thanks a lot!!
Raymond D'Anjou wrote:
> "Star" <star@.nospam.com> wrote in message
> news:%23RPA0n29FHA.2816@.tk2msftngp13.phx.gbl...
>
>
> I agree with Trey on this one.
> One thing that you mentionned "or a Vehicle with an address".
> It sounds silly to me asking "what's the address of the vehicule".
> The vehicle should be linked to a person.
> (I'm not sure if the vehicle can be owned or registered by more than one
> person.)
> Once you have the owner, it's easy to find the address or addresses.
>|||"Star" <star@.nospam.com> wrote in message
news:eVN1el39FHA.2184@.TK2MSFTNGP10.phx.gbl...
> If we have one relationship table for each pair, we would have 380 (19*20)
> relationship tables, and I don't think that would be a good idea.
I've developed databases with more tables than that. The limit in SQL Server
is more than 2 billion objects.
An alternative may be the classic "type/supertype" model. See the following
example. Notice that the keys ensure each entity is of exactly one type.
This can get much more sophisticated by adding the entity_types to the
joining table (entity_references) so that you can enforce rules allowing
only certain combinations of references. Also you may want to add a column
to determine what kind of reference is defined in each case.
CREATE TABLE entities (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL CHECK (entity_type IN ('AD','VH','PR') /*
Address/Vehicle/Person */), UNIQUE (entity_code, entity_type)) ;
CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL REFERENCES
entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENCES entities
(entity_code)) ;
CREATE TABLE addresses (entity_code INTEGER NOT NULL, entity_type CHAR(2)
NOT NULL DEFAULT 'AD' CHECK (entity_type = 'AD'), FOREIGN KEY (entity_code,
entity_type) REFERENCES entities (entity_code, entity_type), /* address
attributes . . . */);
CREATE TABLE vehicles (entity_code INTEGER NOT NULL, entity_type CHAR(2) NOT
NULL DEFAULT 'VH' CHECK (entity_type = 'VH'), FOREIGN KEY (entity_code,
entity_type) REFERENCES entities (entity_code, entity_type), /* vehicle
attributes . . . */);
CREATE TABLE persons (entity_code INTEGER NOT NULL, entity_type CHAR(2) NOT
NULL DEFAULT 'PR' CHECK (entity_type = 'PR'), FOREIGN KEY (entity_code,
entity_type) REFERENCES entities (entity_code, entity_type), /* person
attributes . . . */);
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OPUHrE49FHA.1028@.TK2MSFTNGP11.phx.gbl...
Oops. I missed out a key:
CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL REFERENCES
entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENCES entities
(entity_code), PRIMARY KEY (entity_code_1, entity_code_2)) ;
David Portas
SQL Server MVP
--|||... in fact I missed out nearly all the keys! For completeness, here it is
again:
CREATE TABLE entities (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL CHECK (entity_type IN ('AD','VH','PR') /*
Address/Vehicle/Person */), UNIQUE (entity_code, entity_type)) ;
CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL PRIMARY KEY
REFERENCES entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENCES
entities (entity_code)) ;
CREATE TABLE addresses (entity_code INTEGER NOT NULL PRIMARY KEY,
entity_type CHAR(2) NOT NULL DEFAULT 'AD' CHECK (entity_type = 'AD'),
FOREIGN KEY (entity_code, entity_type) REFERENCES entities (entity_code,
entity_type), /* address attributes . . . */);
CREATE TABLE vehicles (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL DEFAULT 'VH' CHECK (entity_type = 'VH'), FOREIGN KEY
(entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
/* vehicle attributes . . . */);
CREATE TABLE persons (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL DEFAULT 'PR' CHECK (entity_type = 'PR'), FOREIGN KEY
(entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
/* person attributes . . . */);
Hope this helps.
David Portas
SQL Server MVP
--|||A few things:
* Nothing wrong with 380 tables, if you need them all.
* Anything that doesn't strongly define and relate entities is going to
lead to problems.
* However your users think things should be related will drive design,
of course, but their definition of "related" could be different than a
rdbms definition. There will almost certainly be more attributes to
these n-n relations than simply x.id(n)--y.id(n) [ simple example:
person-phone may have phone number type (home,work,mobile,fax,etc.)
which truly belongs with this relationship and not the phone number
itself ]. From your one example, Address-Vehicle still seems to not be a
true n-n relationship - it seems more like "address found" is an
attribute of the relationship of "vehicles involved in a case" (which
itself is an n-n relation between cases-vehicles) - after all, what use
is "address where vehicle found" if it doesn't relate to a case?
(assuming there is such entity).
Only thorough business analysis will help you determine this.
Star wrote:
> Hey folks,
> Thanks for you answers.
>
> Unfortunately not on this system. This systems tracks all kind of
> information. Let's say that for example there was a robbery and the
> vehicle was found at a certain location. That's why we may have all kind
> of weird relationships.
> I agree with you guys in having a relationship table for each pair that
> we may think that are going to be related, but as I said our users may
> have at any moment the need of adding a relation between two very
> different kind of data.
> If we have one relationship table for each pair, we would have 380
> (19*20) relationship tables, and I don't think that would be a good idea.
> I will try to convince my supervisors of only having the relationships
> that we really know that are going to exist (that's what I have always
> wanted), but that's not going to be easy.
> By the way, I thought about another solution. Not very good either, but
> I would like to have your opinion.
> Here it is:
> We do have the 20 tables. Persons, Addresses, ...
> Each one of these tables look like this:
> Table X
> --
> PK (autoincrement)
> [Rest of the fields]
> We assign an ID to each one of these tables. For example, for Persons is
> 1, for Addresses is 2 and so on
> Now we have only 1 relationship table and it looks like this:
> Table Relations
> --
> SourceID
> FK_Source
> DestinationID
> FK_Dest
> [other fields]
> The PK of this table is (SourceID,FK_Source,DestinationID,FK_Des
t)
> SourceID and DestinationID are going to have the ID of one of the 20
> tables respectively. FK_Source and FK_Dest are the PKs of one of the 20
> tables respectively
> So if we have in that Relations table something like (1,32123,2,543543)
> We know that we have a relation between a person and a address. The PK
> of this person is 32123 and the PK of the address is 543543.
> The relation between 2 tables changes depending on the type of data.
> Not really a relational database...
> What do you guys think?
> Thanks a lot!!
>
>
>
>
>
>
>
> Raymond D'Anjou wrote:
>|||Thanks a lot, David!
I haven't tried it yet, I will be doing some testing through the
w
end. My first impression is your idea is something similar to mysecond solution, but will all the relations defined correctly. Please
correct me if I'm wrong.
I'll get back to you in a couple of days, when I do some other testing.
Thanks again.
David Portas wrote:
> ... in fact I missed out nearly all the keys! For completeness, here it is
> again:
> CREATE TABLE entities (entity_code INTEGER NOT NULL PRIMARY KEY, entity_ty
pe
> CHAR(2) NOT NULL CHECK (entity_type IN ('AD','VH','PR') /*
> Address/Vehicle/Person */), UNIQUE (entity_code, entity_type)) ;
> CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL PRIMARY KEY
> REFERENCES entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENC
ES
> entities (entity_code)) ;
> CREATE TABLE addresses (entity_code INTEGER NOT NULL PRIMARY KEY,
> entity_type CHAR(2) NOT NULL DEFAULT 'AD' CHECK (entity_type = 'AD'),
> FOREIGN KEY (entity_code, entity_type) REFERENCES entities (entity_code,
> entity_type), /* address attributes . . . */);
> CREATE TABLE vehicles (entity_code INTEGER NOT NULL PRIMARY KEY, entity_ty
pe
> CHAR(2) NOT NULL DEFAULT 'VH' CHECK (entity_type = 'VH'), FOREIGN KEY
> (entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
> /* vehicle attributes . . . */);
> CREATE TABLE persons (entity_code INTEGER NOT NULL PRIMARY KEY, entity_typ
e
> CHAR(2) NOT NULL DEFAULT 'PR' CHECK (entity_type = 'PR'), FOREIGN KEY
> (entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
> /* person attributes . . . */);
> Hope this helps.
>sql
