DB Design-Going in circles
-
Sounds like you need some many-to-many joins Okay - Let's try and put this in atomic bullet points. * Address can belong to an individual * Address can belong to a company * Person can own 0, 1 or more companies * Company can employ 0,1 or more persons * Person can have a work address * Person can have a home address * A person can be employed by 0, 1 or more companies. So, you have a person table, a company table and and address table. You also have some many-to-many joins which require intermediate tables, so you have a person_owns_company table and a company_employs_person table. These intermediate tables take the primary key from each side of the join and combine them together to make a compound primary key of its own. So, if you have a PersonID and and CompanyID your person_owns_company table will have a compound primary key of PersonID and CompanyID. They also remain foreign keys back to their originating tables. A person can have a homeAddressID column which will be a foreign key to the address table A company can have an addressID column which, again, will be a foreign key for the address table. You may wish to extend this futher as companies can have more than one location and the addressID may be better placed on a company_location table. Does this help?
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
This helps. Interesting that you brought in the location...I thought about it after I posted the question and ended with a headache. How would you handle if you wanted to specify the location for the person's workplace. Say Jill works for company foo, which have 5 locations, and Jill is in location 3. In other words, how would you tie person with company location using the above scenario? And Thanks!
-
This helps. Interesting that you brought in the location...I thought about it after I posted the question and ended with a headache. How would you handle if you wanted to specify the location for the person's workplace. Say Jill works for company foo, which have 5 locations, and Jill is in location 3. In other words, how would you tie person with company location using the above scenario? And Thanks!
Two ways I can think of depending on what sort of data you need. 1. The person has a join to company_location rather than company (the company can be infered from the location) 2. If you have the idea that many companies can occupy the same location, for example a number of small companies in a business centre. The you would join the person to the company and the location.
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Two ways I can think of depending on what sort of data you need. 1. The person has a join to company_location rather than company (the company can be infered from the location) 2. If you have the idea that many companies can occupy the same location, for example a number of small companies in a business centre. The you would join the person to the company and the location.
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Thanks Colin, This helps a lot. The clear, logical and objective perspective is what i needed. And I looked at your Blog - browsed quickly through Normalising the data model. Looks very informative, will go and read it again. Its cool.
hp108 wrote: This helps a lot. Great! I'm glad to have helped. :-D
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Sounds like you need some many-to-many joins Okay - Let's try and put this in atomic bullet points. * Address can belong to an individual * Address can belong to a company * Person can own 0, 1 or more companies * Company can employ 0,1 or more persons * Person can have a work address * Person can have a home address * A person can be employed by 0, 1 or more companies. So, you have a person table, a company table and and address table. You also have some many-to-many joins which require intermediate tables, so you have a person_owns_company table and a company_employs_person table. These intermediate tables take the primary key from each side of the join and combine them together to make a compound primary key of its own. So, if you have a PersonID and and CompanyID your person_owns_company table will have a compound primary key of PersonID and CompanyID. They also remain foreign keys back to their originating tables. A person can have a homeAddressID column which will be a foreign key to the address table A company can have an addressID column which, again, will be a foreign key for the address table. You may wish to extend this futher as companies can have more than one location and the addressID may be better placed on a company_location table. Does this help?
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Colin Angus Mackay wrote: so you have a person_owns_company table and a company_employs_person table Question: Would it be better to add the above two tables or one one table called "PersonCompany" with foreignkeys: PersonID and CompanyID and one field called "RelationType" eg: IsEmployee or IsOwner. (kind of like a crossreference). Of course this would also be two tables if I introduce a separate table called "TypeTable" and use it to lookup the "RelationType". Any suggestions?
-
Colin Angus Mackay wrote: so you have a person_owns_company table and a company_employs_person table Question: Would it be better to add the above two tables or one one table called "PersonCompany" with foreignkeys: PersonID and CompanyID and one field called "RelationType" eg: IsEmployee or IsOwner. (kind of like a crossreference). Of course this would also be two tables if I introduce a separate table called "TypeTable" and use it to lookup the "RelationType". Any suggestions?
hp108 wrote: Would it be better to add ... one table called "PersonCompany" with foreignkeys: PersonID and CompanyID and one field called "RelationType" I wouldn't have that as one table with a RelationType column unless there were going to be many queries that didn't care what the relation was, just that they were related in some way. The reason for this is that these are two distinct logical relationships and the physical side should match unless there are performance problems. In otherwords, design the model as normalised as possible, then denormalise it afterwards if you find performance problems. If you do decide to put it only one table, remember that the RelationType column is most likely to become part of the Primary Key if you can have a person that shares both types of relation with the company.
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
hp108 wrote: Would it be better to add ... one table called "PersonCompany" with foreignkeys: PersonID and CompanyID and one field called "RelationType" I wouldn't have that as one table with a RelationType column unless there were going to be many queries that didn't care what the relation was, just that they were related in some way. The reason for this is that these are two distinct logical relationships and the physical side should match unless there are performance problems. In otherwords, design the model as normalised as possible, then denormalise it afterwards if you find performance problems. If you do decide to put it only one table, remember that the RelationType column is most likely to become part of the Primary Key if you can have a person that shares both types of relation with the company.
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
I believe you have a point because: (quick type, excuse mistakes - just to make a point) select p.*, c.companyname from person p inner join personcompany pc on p.personid = pc.personid inner join company c on pc.companyid = c.companyid where pc.relationtype="IsEmployee" group by c.companyid would give the same result as the following which does not use the where clause: select p.*, c.companyname from person p inner join company-employs-person pc on p.personid = pc.personid inner join company c on pc.companyid = c.companyid group by c.companyid I'll experiment with both and see which is faster.Thanks.
-
Sounds like you need some many-to-many joins Okay - Let's try and put this in atomic bullet points. * Address can belong to an individual * Address can belong to a company * Person can own 0, 1 or more companies * Company can employ 0,1 or more persons * Person can have a work address * Person can have a home address * A person can be employed by 0, 1 or more companies. So, you have a person table, a company table and and address table. You also have some many-to-many joins which require intermediate tables, so you have a person_owns_company table and a company_employs_person table. These intermediate tables take the primary key from each side of the join and combine them together to make a compound primary key of its own. So, if you have a PersonID and and CompanyID your person_owns_company table will have a compound primary key of PersonID and CompanyID. They also remain foreign keys back to their originating tables. A person can have a homeAddressID column which will be a foreign key to the address table A company can have an addressID column which, again, will be a foreign key for the address table. You may wish to extend this futher as companies can have more than one location and the addressID may be better placed on a company_location table. Does this help?
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Colin Angus Mackay wrote: A person can have a homeAddressID column which will be a foreign key to the address table A company can have an addressID column which, again, will be a foreign key for the address table. Sorry for the bother! Does this mean that "homeAddressID" in the address table will be null when the address belongs to the company ( and vice versa ) ?
-
Colin Angus Mackay wrote: A person can have a homeAddressID column which will be a foreign key to the address table A company can have an addressID column which, again, will be a foreign key for the address table. Sorry for the bother! Does this mean that "homeAddressID" in the address table will be null when the address belongs to the company ( and vice versa ) ?
No, the address table would have an AddressID column which would be its primary key. The Address table does not need to know that it is a home address or office address. It is just an address. The person table may have a homeAddressId column (indicating that it is the home address of the person). A person may have a workAddressID as well (if that is your design - although it may be inferred by their links to a company or companyLocation) The Address table would be part of a one-to-many join, for every one address there are many people or many companies or both. The Address table cannot track that, so the tracking goes to the person or company table. Something like this:
ParentTable (Address) ChildTable (Person)
ParentId <----\ ChildID
OtherColumns \---> ParentID
OtherColumns
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
No, the address table would have an AddressID column which would be its primary key. The Address table does not need to know that it is a home address or office address. It is just an address. The person table may have a homeAddressId column (indicating that it is the home address of the person). A person may have a workAddressID as well (if that is your design - although it may be inferred by their links to a company or companyLocation) The Address table would be part of a one-to-many join, for every one address there are many people or many companies or both. The Address table cannot track that, so the tracking goes to the person or company table. Something like this:
ParentTable (Address) ChildTable (Person)
ParentId <----\ ChildID
OtherColumns \---> ParentID
OtherColumns
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Design wise this makes sense, but in practice this would mean that I have to add the address record before I could refer to it from the person or company table, which seems backward as it would allow me to add addresses without requiring it be used. So I could end up with addresses which are orphaned (as no one refers to them). I am new at this so I do not know if this is normal pattern?
-
Design wise this makes sense, but in practice this would mean that I have to add the address record before I could refer to it from the person or company table, which seems backward as it would allow me to add addresses without requiring it be used. So I could end up with addresses which are orphaned (as no one refers to them). I am new at this so I do not know if this is normal pattern?
Normally I use stored procedures for everything, so in a situation like this the stored procedure would determine if an existing address existed and join to that, or if a new address needed to be created and then create and join to the new address. It would do this all in one transaction, so that if the later part of the transaction failed (for example creating a new row in the Person table) then the whole thing would be rolled back and it would be as if there never was an Address record. In the reverse situation, you could always add a trigger to the person or company/companyLocation table so that in the event that it is being deleted the trigger can see if there is anything else joined to the address and remove the address if it would be orphaned. Personally, I'm not a big fan of triggers (I've only used them for building audit information) as I prefer all access through stored procedures. There will always be weak points in a design but you you'll know how you intend to use it and can compromise on certain things. For example, if you will be mostly reading (SELECTing) data then you can optimise it by putting indexes on the columns most often referenced in your WHERE clauses, however, by doing this you will slow down INSERT, UPDATE, DELETES. I hope this helps (I may have gone off at a tangent a little in my last paragraph).
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Normally I use stored procedures for everything, so in a situation like this the stored procedure would determine if an existing address existed and join to that, or if a new address needed to be created and then create and join to the new address. It would do this all in one transaction, so that if the later part of the transaction failed (for example creating a new row in the Person table) then the whole thing would be rolled back and it would be as if there never was an Address record. In the reverse situation, you could always add a trigger to the person or company/companyLocation table so that in the event that it is being deleted the trigger can see if there is anything else joined to the address and remove the address if it would be orphaned. Personally, I'm not a big fan of triggers (I've only used them for building audit information) as I prefer all access through stored procedures. There will always be weak points in a design but you you'll know how you intend to use it and can compromise on certain things. For example, if you will be mostly reading (SELECTing) data then you can optimise it by putting indexes on the columns most often referenced in your WHERE clauses, however, by doing this you will slow down INSERT, UPDATE, DELETES. I hope this helps (I may have gone off at a tangent a little in my last paragraph).
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
I have a question - I am trying to design a database - contacts - and I have seen many around but do not serve my purposes - so I started with my requirements: The logical breakdown where: - the adress stored could belong to an individual or company. - the relationship of person to company could be one person can own multiple companies but one company could be the employer of many persons. - Thus - the address could be the address of the company but also of the person's work address or the address could be the persons personal address (and have nothing to do with the company - say an unemployed alcoholic who is a good friend). I am gong in circles - any suggestions? I have not even started into the phone table yet!
I think you are taking normalization too far. Dont concern yourself as to storing addresses more than once. The address in each table I create is usually just an attribute. This means I would have an address for each company and person. Same with phone. Although it is possible that a company and person might have the same address or phone it isnt important that they are the same physical record in some table. "Owning" a company - you work for it. I see three tables in your entire post. companies -> companies_people <- people This way people can be part of a company or NOT. Company table would have a main phone, fax etc and address(s) People would have their own ID, address and phone. YES you might have duplicated data but it certainly could not be called a transitive dependancy. http://www.jasncab.com/huberblog :: Jason Huber
-
I think you are taking normalization too far. Dont concern yourself as to storing addresses more than once. The address in each table I create is usually just an attribute. This means I would have an address for each company and person. Same with phone. Although it is possible that a company and person might have the same address or phone it isnt important that they are the same physical record in some table. "Owning" a company - you work for it. I see three tables in your entire post. companies -> companies_people <- people This way people can be part of a company or NOT. Company table would have a main phone, fax etc and address(s) People would have their own ID, address and phone. YES you might have duplicated data but it certainly could not be called a transitive dependancy. http://www.jasncab.com/huberblog :: Jason Huber
Thanks. I agree that the database does not heve to be normalised to the smallest detail as it would effect perfomance and make the queries more complex. But like Colin pointed out: In otherwords, design the model as normalised as possible, then denormalise it afterwards if you find performance problems. I want to take this one step further - not denormalise it but use its structure to dynamically create only the required tables/relations. It might be a good idea to kind of explain what I am trying to do. The model I am trying to build will not actually contain any data, but its structure will be used (schema - which could be in xml format) to generate a database that will only contain the tables and structure required by the user. For example: User may want to store a telephone list - name and all the related phone numbers - so I would pick only the necessary tables (person,phonegroup,phone) and their relationships from the schema and create the database. Then he might want another DB to have all company info... So the idea is to "morph". For this I need all the conceivable possibility for the schema. Now I know this may not be the best approach for this kind of application and suggestions are welcome. Thanks!