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 |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2002-05-07 : 11:57:33
|
| Once again I'm struggling with a design question:I have a sql database and some tables. In these days we have a lot of ways to communicate with customers. Faxes, Telphones, Mobiles, Email-addresses, Sites, etc.I think it's silly to design a table like this:BusinessName VARCHAR (50)BusinessStreet VARCHAR (100)Telephone VARCHAR (20)Fax VARCHAR (20)Email VARCHAR (20)What happens.... my boss decides he wants a field Mobile number.. And then websiteadress and then I-mode number... You'll get my drift.So I thought I make a table Media and use a Link-table between Customers and Media.Table Clients:ClientID (1...2...3...4 etc.)ClientName (McDonalds, MG, Walt Disney, etc.)Table Media:MediaId (1..2..3..4..5)MediaValue ( 555-8927374 , 1800-4653874, henrikop@planet.nl, +31-6-26112194, etc.)MediaType (Telephone, Fax, Email, Website, Mobile, etc.)MediaAttribute (General, Order Entry, Financial, Working Hours, Evening, etc.)Table ClientMedia:ClientMediaID (1...2..3..4...)ClientIdMediaIdNow one client can have several phone numbers, faxmachines, email adresses, etc.I'm still in design phase.My question is: Is this common practice to build my information this way, or are there better ways to do it???It's very hard to find 'best practice' examples on database design what-so-ever. So your input will be highly appreciated.Henri~~~SQL is nothing, writing it everything. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-07 : 13:21:24
|
You've definitely got the right idea, but the structure I'm recommending is a little different from yours. I'm basically combining the Media and ClientMedia tables into one:CREATE TABLE ClientMedia (ClientID int NOT NULL FOREIGN KEY REFERENCES Clients (ClientID),MediaType varchar(20) NOT NULL, MediaValue varchar(50) NOT NULL, MediaAttribute varchar(50)CONSTRAINT PK_ClientMedia PRIMARY KEY (ClientID, MediaType, MediaValue)Instead of putting phone numbers etc. into a table and then linking the phone number to a customer in ANOTHER table, I think keeping them together like this is better. I also got rid of the ID/Identity columns (what a shocker!)  because you absolutely don't need them, especially with this table. The combination of MediaType, MediaValue, and ClientID should be unique anyway, so it makes a perfect primary key, and if you keep the data in one table you'll only need to link to other tables through the ClientID column. You can store the same phone number for the same client more than once (shared home/fax number), just use a different media type. And while it's unlikely that 2 different customers will share an email or phone number, you can still support it with this structure using different client IDs.HTH |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-07 : 13:23:20
|
| We use the "link" table concept in our system. Seems to work pretty well for us, and it's pretty flexible. I've not found any performance issues with doing it this way either. I think it's a BIT slower, but it gives you a ton more flexibility. Adding a field to a table is not fun after a system has been live for six months, but dropping a record in a table in the same system is really easy.Michael |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2002-05-07 : 16:11:15
|
| Thx for replying.The thing with the link-table is because of the next issue: I want to use this media with Employees as well as Prospects, so basically there's only one table with media information. This is especially usefull for SQL Functions, and searchfunctions. At the other end... Now I have more than one Link table....BTW I'm developing in ASP.NET. As a treat I used a snitz forum in my solution. However, I noticed that performance of aspx pages are WAY better than traditional ASP pages. There's less CPU use on my server and also for the user it's faster.Anyway, thx for your insight, this put me on the right foot.Henri~~~SQL is nothing, writing it everything. |
|
|
|
|
|
|
|
|
Media Design