|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help developing this project.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: Show quote > 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! >
Show quote
"Star" <star@nospam.com> wrote in message I agree with Trey on this one.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! 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_Dest) 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: Show quote > "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. > > "Star" <star@nospam.com> wrote in message I've developed databases with more tables than that. The limit in SQL Server 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. 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_dpor***@acm.org> wrote in message Oops. I missed out a key:news:OPUHrE49FHA.1028@TK2MSFTNGP11.phx.gbl... 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 -- Thanks a lot, David!
I haven't tried it yet, I will be doing some testing through the weekend. My first impression is your idea is something similar to my second 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: Show quote > ... 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. > 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: Show quote > > 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_Dest) > > 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... >> >>> 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. >> >> Hi Trey,
Thanks for your help. Yes, you are completely right, one of our entities is 'Case' and they want to associate different type of data to a particular case. Let's see if I understand everything, when you say... > [ simple example: Do you mean that we should have inside the Person table one attribute> person-phone may have phone number type (home,work,mobile,fax,etc.) > which truly belongs with this relationship and not the phone number > itself ] called 'Phone' and another one called 'PhoneType' instead of having a separated table? > it seems more like "address found" is an The same here? Should I have an attribute inside the Vehicles table > attribute of the relationship of "vehicles involved in a case" (which > itself is an n-n relation between cases-vehicles) called 'Address' instead of having a relation with an Address table? If you answer YES to both questions, my concern is the following: If the user wants to make a query like 'Give me all the information that you have about the address "100 Main st"' (I mean, robberies, persons living there, ... ) Wouldn't it be redundant? For example, we could have an Address record '100 Main st' and maybe inside the Vehicles table, in the 'Address' attribute have '100 Main st' as well. It would be difficult to do some link analysis with this design. I hope you can understand me... my English is not very good. Thanks Trey Walpole wrote: Show quote > 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. >> >> > 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_Dest) >> >> 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... >>> >>>> 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. >>> >>> answers in-line below...
Star wrote: Show quote > Hi Trey, No - i mean that you would have a linking table between Person and Phone > > Thanks for your help. > > Yes, you are completely right, one of our entities is 'Case' and they > want to associate different type of data to a particular case. > > Let's see if I understand everything, when you say... > > > [ 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 ] > > Do you mean that we should have inside the Person table one attribute > called 'Phone' and another one called 'PhoneType' instead of having a > separated table? > - e.g., called PersonPhone. In that linking table, you would have the PersonID, PhoneID, and another attribute PhoneType - i.e., the type of phone number is an attribute of the relationship between Person and Phone. Make sense? > Still a linking table. I was thinking more that Cases would have a > > 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) > > The same here? Should I have an attribute inside the Vehicles table > called 'Address' instead of having a relation with an Address table? > related table for vehicles involved in the case [CasesVehicles, e.g.] and that table could have a column [AddressWhereFound, e.g.] referencing the Address table. > At that point, you would certainly need to know all the possible tables > If you answer YES to both questions, my concern is the following: > If the user wants to make a query like 'Give me all the information that > you have about the address "100 Main st"' (I mean, robberies, persons > living there, ... ) > > Wouldn't it be redundant? For example, we could have an Address record > '100 Main st' and maybe inside the Vehicles table, in the 'Address' > attribute have '100 Main st' as well. It would be difficult to do > some link analysis with this design. > that have an Address reference. Such involved queries are typically best not left up to users to put together, as they can easily write queries that bog the system down. Also, such a request would really be multiple requests combined into a larger report, anyway. My concern would be that, without tight entity relationships, data analysis could be meaningless or inaccurate. For example, just going off the address example above: Suppose this address had a robbery that occurred there, and that the person who lives there committed a robbery themselves [at another address]. If the design is too loose, it could be possible to interpret this as two robberies related to this address, and miss that only one of them actually occurred there; and the other robbery actually relates to the person living at that address. > I hope you can understand me... my English is not very good. Never would have known - seems pretty good to me. :)> Show quote > Thanks > > > > > > > > > > > > > Trey Walpole wrote: > >> 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. >>> >>> > 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_Dest) >>> >>> 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... >>>> >>>>> 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. >>>> >>>> > Trey Walpole wrote: Thanks Trey.I understand much better now. I'm going to spend some time trying to define all the entities, relationships and queries that we may have. I will take into consideration all your ideas. I really appreciate all your help. Regards Star wrote:
Show quote > Hi, Not too many. Just as many as are needed - no more and no less. I> > 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. > 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 -- I'll throw out my $0.02...
In our system, we have a "common customer database". Basically, we have a long list of customers, and those customers can be attached to any number of different accounts (in our system, property tax accounts, utility accounts, dog licences, business licences, etc). So what we have is a customer subleger table, that has the customer number, the account identifier, and then an integer to define the type of account (and another one for the type of relationship). So for example, you might see something like: Customer Number Account ID Series OwnerType WALLY001 1000 1 1 WALLY001 15 2 1 SMITH001 1000 1 2 BROWN001 2000 3 1 Now the above records would imply that WALLY001 and SMITH001 are owners of a property tax account, with WALLY001 being the primary owner, and SMITH001 being a secondary owner. WALLY001 is also the primary owner of a utility account, and BROWN001 is a primary owner of a dog licence. Anyway, I thought I'd throw that out there. It means that we end up doing a lot more logic on the application side (foreign key constraints enforced through code, not the database), but it works for us. Plus the application we develop against doesn't support cool things like foreign key constraints anyway. :) -- Show quoteClint "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! > On Fri, 02 Dec 2005 18:58:04 +0100, Star <star@nospam.com> wrote:
>This is the problem: You don't.> >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? This is what relational databases are all about! Just put your facts in the tables, and let SQL queries create the relationships on the fly. Only if this does not give satisfactory performance, do you have to worry about doing anything fancier. Josh jxstern wrote:
> Just put your facts in the tables, and let SQL queries create the Sorry I get back to you so late. I hope you still get my message. What> relationships on the fly. > do you mean with 'create relationships on the fly'? Could you please provide an example? Thanks I am about to start with this project but I'm a little bit worried of creating one relationship table for each pair, that would end up with 380 (19*20) relationship tables. My supervisor insists that he wants to have the possibility of relating everything with everything. Thanks  |
|||||||||||||||||||||||
Other interesting topics