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 |
|
Rob555
Starting Member
5 Posts |
 Posted - 2008-10-03 : 22:25:10
|
| Hello everyone,I have a basic question about table/relationship design. I'm creating a database storing information about various businesses. I'm having trouble figuring out the best way to link the address information to the table containing the business name and related information.Each business can have up to three addresses: business address, mailing address, and the address of the corporate headquarters.I'm thinking about creating a many to many relationship and adding an AddressType column to the address table.I'm interested in any comments or suggestions you may have.Thanks, Rob |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-04 : 07:33:15
|
| will each business record values for all the three addresses? |
 |
|
|
Rob555
Starting Member
5 Posts |
Posted - 2008-10-04 : 15:25:42
|
| The database specifications don't require each business to have three addresses, but the database should be capable of associating three addresses to each business.Each business is required to have at least one address. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-10-04 : 16:06:17
|
| A typical way to model a many-to-many relationship is with a relationship table ie: [BusinessAddress]. So at a minimum you would have columns: BusinessID, AddressID, AddressTypeID. That way any number of businesses could share the same addressID and a business could have any number of addresses.Be One with the OptimizerTG |
|
|
|
Rob555
Starting Member
5 Posts |
Posted - 2008-10-04 : 19:21:43
|
| Thanks for the suggestion.So the BusinessAddress table will connect the Business table and Address table.The Business table primary key will be BusinessID and the Address table primary key will be AddressID. The BusinessAdress table will have the three primary keys that you mentioned.TG, is that what you had in mind? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-05 : 01:21:03
|
| yup. thats a good suggestion. and by means of TypeID you determine if its a business or mailing or corporate address. |
|
|
|
Rob555
Starting Member
5 Posts |
Posted - 2008-10-05 : 09:31:44
|
| Thank you TG and vosakh16 for your suggestion. I am making the move to SQL 2005 after using MS Access for the last 6 years. I hope to purchase modeling software sometime this fall. For now, I'm using the drawing tools in Microsoft Word to draw the table relationships.Rob |
|
|
|
|
|
|
Multiple Addresses