Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nlocklin
Yak Posting Veteran
69 Posts |
 Posted - 2002-10-28 : 12:44:35
|
| I'm designing a database that will store information about people, addresses, organizations, and people's roles within the organization.The tables so far look something like:tblPerson (PK PersonID)tblAddress (PK AddressID)tblOrg (PK OrgID)tblRole (PK RoleID)Most of the relationships are many-to-many, so I added join tables:tblPersonAddress (PK PersonAddressID, FK PersonID, FK AddressID)tblPersonOrg (PK PersonOrgID, FK PersonID, FK OrgID)tblPersonOrgRole (PK PersonOrgRoleID, FK PersonOrgID, FK RoleID)(a person can have multiple roles at a single organization)Where I'm stuck is relating a person's address to an organization. I'd like to use the table tblPersonAddress in the join because it contains a lot of extra information about a person being at an address and obviously I shouldn't have those data in two places. A person can have multiple addresses (at the same organization, different organizations, or both), and these addresses do not necessarily have to be associated with an organization.If I link tblPersonAddress to tblOrg through a new table, then there are two relational paths from a person to an organization (through an address or through a role) which could be confusing. If I link tblPersonAddress to tblPersonOrg through a new table then PersonID is in two places and there's the possibility of someone possibly selecting the wrong PersonAddress record and having conflicting PersonID's.I'm not sure what the best way to relate these tables is. Does anyone have any advice or suggestions? Thanks in advance.--"It's not that I'm lazy... it's that I just don't care." |
|
|
DavidD
Yak Posting Veteran
73 Posts |
Posted - 2002-10-28 : 19:03:15
|
| Don't know if our situation is relevant to yours, but we have the organisation and address tables combined, and then an equivalent of a tblpersonaddress table to link the person to his address with a field which describes if this address is his main one, mailing one etc. (it involves doctors and the various clinics they work out of). Anyway it does work quite well for us, but may not in your situation.RegardsDavid |
 |
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-10-29 : 05:26:08
|
| Assumptions 1) People, Orgs, Roles and Addresses can all exist independently of each other 2) A position is created when a person fills a role at an organisation. Relationships 1) People -> Positions 2) Organisations -> Positions 3) Roles -> Positions 4) People <-> Addresses (eg: mailing, residential etc.) 5) Organisations <-> Addresses (eg: office, billing, shipping etc.) note that 4&5 are independent of any positions that may exist or not. So that is all straight forward. But now we would have to be more clear on the rules for addresses if they are related to positions. Can a position have any address at all? Must a position's address correspond to a "location" within an organisation ? If you can give the rule we can easily model it I think. hope that helps a little at least. ----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-10-30 : 13:37:55
|
| A couple of comments on your proposed design...1) Creating a separate PK on your join tables (like tblPersonAddress = PK PersonAddressID, FK PersonID, FK AddressID) allows there to be multiple records for this PersonID and AddressID combination, which in most cases would be improper duplicate data. Instead, make the PK a 2-part key consisting of the PersonID and AddressID together.2) The tblPersonOrg and tblPersonOrgRole may not need to be separated into two different tables. Rather, add the RoleID as a field in the tblPersonOrg, and again make the PK a composite key consisting of all 3 fields. Unless you have other data that you will be tracking relevant to that Person X Org combination that would be redundant across every role that person has. If that is the case, then split them apart like you propose and refer to comment #1 above. |
|
|
|
|
|
|
|
|
Relational tables advice