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 |
|
Gurj
Starting Member
6 Posts |
 Posted - 2007-05-25 : 03:59:27
|
| Hi, this is my first post to this forum so thank you all in advance..I am trying to design a database to store information about the Specification required by each customer. And the main problem I am having is how to store 2 instants of ContactID (from CustomerContacts table) in a seperate table called CustomerSpec. For example each Customer has many contacts,(the Customer data is stored in a table called Customers which has a one to many relationship with the CustomerContacts table) each customer has one and only one Customer spec, and each customer spec needs to have 2 customer contacts, ie. one for Artwork and one production. (it should also be possible to have the same contact for both Artwork and production).The problem is how to associate these contactId's with the customer spec...(if there was only one Contact per spec I could simply link the CustomerContacts table with the CustomerSpec table and drop ContactID into CustomerSpec as a foreign key.. But I am stuck to how to save more than one ID..Can you help please.. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 04:16:11
|
| Can't your CustomerSpec table have two columns:ArtworkContactID and ProductionContactID??However, if you consider that there might be more "associated contacts" in the you could make a table for them:CustomerID, ContactID, SpecID, Type (i.e. "Artwork", "Production", ...)Kristen |
 |
|
|
Gurj
Starting Member
6 Posts |
Posted - 2007-05-25 : 04:24:41
|
| Yes customer spec could have 2 coloums, but I am unsure how to link them to the Customer Contacts table.. Ie. if a contact was deleted from the customer contacts table would link to the e.g ArtworkID field also be deleted? and if so what is the relationship between the tables..The problem with having "type" is how to incorporate both, ie one contact could be responsible for both Artwork and Production for a small company.. I new to databases so I could be missing something obvious.. |
|
|
|
Gurj
Starting Member
6 Posts |
Posted - 2007-05-25 : 05:47:32
|
| right i think i have found a solution..I've added Artwork and Production attributes to the CustomerContacts table and assigned them default values of 0..Now if a contact is selected as a Artwork contact in the form, this ID will b updated to 1 and same for Production..I think this should work.. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 06:55:03
|
Not sure where you are storing the Artwork / Production ContactIDs, but the "absence" of them would be a better indicator than the 0/1 Attribute. (The reason being that if you have BOTH an Artwork ContactID AND the Artwork attribute = 0 which do you believe?  This would probably only come about because of a software bug .. but ...  )"customer spec could have 2 columns, but I am unsure how to link them to the Customer Contacts table"make a Foreign Key for each of them? (Unless I am missing something!)"a contact was deleted from the customer contacts table would link to the e.g ArtworkID field also be deleted?"I don't think that SQL Server would allow you to set up a "cascade delete" in this circumstance because the relationship is either circular or too complicated. So, in the absence of that, ordinary (i.e. NON-cascade) relationships will complain [i.e. the transaction will be "Disallowed", and will raise an error] if you try to delete a Contact and it is used in either the Artwork or Production ContactID columns on the CustomerSpec table.Likewise if you use a "linking table" between Contact and CustomerSpec tables.Kristen |
|
|
|
Gurj
Starting Member
6 Posts |
Posted - 2007-05-25 : 07:18:56
|
decided to tweek it and just as a attribute called "Dept" to the CustomerContact table and assign it either 1 for Artwork, 2 for Production or 3 for both...Thank for you help Kristen  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 07:35:18
|
| Ah, OK. that sounds good. So you've added "Workflow" to the application, that should increase the sticker-price!!edit: Maybe change "3" to "999" to allow more steps in the future? |
|
|
|
|
|
|
|
|
Storing 2 instants of contact in a seperate table