April 12, 2007 at 5:33 pm
I have a single SQL file that I want to normalise.
Fields: CustomerNumber, CustomerName, ContactFirstName, ContactLastName
I want to split into two tables, tblCustomers, tblCustomerContacts. There will be a many-to-many relationship between tblCustomers and tblContacts.
tblCustomers (CustomerID [identity], CustomerNumber, CustomerName)
tblCustomerContacts (ContactID [identity], CustomerID, ContactFirstName, ContactLastName)
The relationship between the two will be tblCustomers.CustomerID = tblCustomerContacts.CustomerID.
Assuming I have this looking fairly ok so far, I am on to the insert.
I have scripted the INSERT routine, and I'm confused on maintaining the CustomerID within tblContacts. The identity insert is on for both tables, so each of their Primary Keys are fine. I'm just not sure how to insert into the Contacts table using the CustomerID from tblCustomers.
This is basic and I've been here before, but it's been years since I've had to build and maintain this relationship.
Any help or direction to references would be great. I'm also having trouble finding related material to review.
Thanks in advance!!
April 12, 2007 at 6:31 pm
Since you said it was a many-to-many, are you actually going to have contacts that span multiple customers?
As for the identity issue, when you insert a new customer, you can get the SCOPE_IDENTITY value and pass that on to the routine that inserts contact records.
April 12, 2007 at 8:18 pm
Can you verbalize your requirements a little better,
You stated that it is a many to many relationship.
a customer has many contacts, but can a contact belong to many customers?
the way you have it, your data will be denormalized.
if it is indeed many to many where
A customer has many contacts,
and a Contact can belong to many customers, then you need 3 tables.
Customer.
CustomerID
Name
Contact.
ContactID
FirstName
LastName
CustomerContacts
CustomerID
ContactID
April 12, 2007 at 11:14 pm
hi
this should be the design for this purpose
"Keep Trying"
April 13, 2007 at 8:59 am
I apologize, I meant many-to-one. Multiple contacts to one customer. I will try out the SCOPE and see if I can't get that to work.
I really appreciate the replies!
April 13, 2007 at 9:23 am
JuanBob, when using SCOPE_IDENTITY, the first thing I always do is pop it into a variable. That way it's reusable throughout the procedure, even if you do other operations which change the SCOPE_IDENTITY value. This is especially useful if you are using that value in multiple inserts, as each of them can potentially change the SCOPE_IDENTITY value, thus messing up the next insert if you don't have it stored.
April 13, 2007 at 9:26 am
OK here are my thoughts first you are talking about people some being customers and some as contacts for those customers.
But I ask this,
Can a customer have more than one contact? (Already answered)
Can a contact also be a customer?
Can a contact ever be related to more than one customer?
If the second and third and always not going to be true then a simple design like you have will work except for one change
tblCustomers (CustomerID [identity], CustomerNumber, CustomerFirstName, CustomerLastName)
tblCustomerContacts (ContactID [identity], CustomerID, ContactFirstName, ContactLastName)
However if the second may ever be true (which I bet it might be) and regardless of the third then something like this
(Note: I dislike prefixs such as tbl personnally you may choose otherwise)
People (Persons_ID [identity], Persons_First_Name, Persons_Last_Name, Customer [bit])
Contacts (Contact_ID [foreign key to Persons_ID in People], Customer_ID [foreign key to Persons_ID in People])
But for me even if the second and third we never true I would go with the later for the just in case scenario so no changes will ever have to be made especially to ensure the names of customers who are contacts state properly updated without causing relational issues.
Also your expressed design is simplistic and doesn't tell me what other data might be involved to know if this does not require a different approach.
April 13, 2007 at 12:05 pm
Again, thanks so much! I have assigned SCOPE_IDENTITY to @scopeIdent which worked great.
As for Antares, working in CRM I have encountered the different scenarios of 2 and 3, so I can certainly relate to and appreciate those points. In this scenario, it is a much smaller scope of requirements so customers are always company entities (Inc, LLC, Corp, etc). Contacts can certainly be associated with other companies, but the requirements are quite simplistic so I'll have to review that further.
Thanks!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply