asked on
Query & Database Help
I'm sure this is really simple but I'm not sure how to go about doing it. Two part question.
I have a table set up that categorizes a contact type (see image). I'm having a problem conceptualizing how I could have a contact that has multiple types associated with it and how I would be able to query based upon the conatact types if there are multiple types associated with a particular contact.
Queston one: How would I set up the table, currently I have ContactStatusID as an int Type and the ContactStatus as a varchar. I'm concerned if I insert the data via a select (list) tag in a form that field ContactStatusID is an int type that I will not be able to use 1,2,3 after it's inserted.
Question two: how would you query something like this. If the contact (separate table) has a ContactStatusID of 1,2,3 how would you query if it has a 2 in it and not all of the other entries.
I hope this makes sense
multiple.gif
I have a table set up that categorizes a contact type (see image). I'm having a problem conceptualizing how I could have a contact that has multiple types associated with it and how I would be able to query based upon the conatact types if there are multiple types associated with a particular contact.
Queston one: How would I set up the table, currently I have ContactStatusID as an int Type and the ContactStatus as a varchar. I'm concerned if I insert the data via a select (list) tag in a form that field ContactStatusID is an int type that I will not be able to use 1,2,3 after it's inserted.
Question two: how would you query something like this. If the contact (separate table) has a ContactStatusID of 1,2,3 how would you query if it has a 2 in it and not all of the other entries.
I hope this makes sense
multiple.gif
Web ServersMySQL Server
Last Comment
jasch2244
You could have a separate row in your contact table for each ContactStatusId/Contact combination. When you do your select, you can either select where the ContactStatusId = a given value, or you can select all the ContactStatusIds for that Contact, and check each one individually.
What you need is another table. I'm assuming you have a Contact table which has a Foreign Key linking to ContactStatusID? If so, what you do is create a table called Contacts_To_Status or whatever. It has two columns; ContactStatusID linking to the ContactStatus table exactly as you have it now, and ContactID linking to your Contact table. Optionally you might have a 3rd column for a Primary Key, but it might not be required. If you need any further information about the Contact-ContactStatus relationship, it might also go into this table (e.g. if you were storing date someone became an Agent or Buyer).
So you have ContactStatus joins to Contacts_To_Status joins to Contacts. Make sense? Your ContactStatus table still only has the six rows. Your Contacts_To_Status table has multiple rows for each Contact's many contact types.
So you have ContactStatus joins to Contacts_To_Status joins to Contacts. Make sense? Your ContactStatus table still only has the six rows. Your Contacts_To_Status table has multiple rows for each Contact's many contact types.
Sorry, forgot to say, if going with the above suggestion, get rid of the ContactStatusID foreign key in your Contacts table.
Also suggest reading up on normalisation.
Also suggest reading up on normalisation.
Agreed, duncancumming's method is a better& more scalable way to accomplish what you are trying to do.
ASKER
Sorry guys I'm still not getting it...
I do have a contact table linking back with a pk to the ContactStatusId table. I was hoping to insert into the contacts table a list of all the ContactStatusID's from the ContactStatusID table. In other words a Contact could be: 3(Buyer), 4(Service Provider), 6(Sphere of Influence). How would you query this if you wanted everyone who was say a 4 being that it's a list of values. Is this possible. I'm trying to avoid setting up another table as I don't fully understand why I would need it.
I do have a contact table linking back with a pk to the ContactStatusId table. I was hoping to insert into the contacts table a list of all the ContactStatusID's from the ContactStatusID table. In other words a Contact could be: 3(Buyer), 4(Service Provider), 6(Sphere of Influence). How would you query this if you wanted everyone who was say a 4 being that it's a list of values. Is this possible. I'm trying to avoid setting up another table as I don't fully understand why I would need it.
ASKER
Ok, a lite just went off in my head about what you are speaking of. That does make sense... completely!
What I am wondering though is can you query a list and only pull out what you need? If the values are say 3,5,6 and you want just 5?
What I am wondering though is can you query a list and only pull out what you need? If the values are say 3,5,6 and you want just 5?
What you have to do is a join between the 3 tables.
Just now I think what you're suggesting is your Contact table has the ContactStatusID column, and it might have in there a list like:
1,2,3
This is called denormalisation, and in this case it's bad. It makes it much harder to maintain. Instead we're going to normalise the data one step further, by (in this example) creating three rows in a different table that are like:
CustomerID StatusID
1 1
1 2
1 3
So if you want to find out all statuses for any customer, you join the three tables together
SELECT Customer.Name, CustomerStatus.Status
FROM Customer
INNER JOIN Customers_To_Status
ON Customer.CustomerID = Customers_To_Status.Custom
INNER JOIN CustomerStatus
ON CustomerStatus.StatusID = Customers_To_Status.Status
Just now I think what you're suggesting is your Contact table has the ContactStatusID column, and it might have in there a list like:
1,2,3
This is called denormalisation, and in this case it's bad. It makes it much harder to maintain. Instead we're going to normalise the data one step further, by (in this example) creating three rows in a different table that are like:
CustomerID StatusID
1 1
1 2
1 3
So if you want to find out all statuses for any customer, you join the three tables together
SELECT Customer.Name, CustomerStatus.Status
FROM Customer
INNER JOIN Customers_To_Status
ON Customer.CustomerID = Customers_To_Status.Custom
INNER JOIN CustomerStatus
ON CustomerStatus.StatusID = Customers_To_Status.Status
That query I just gave will give all customers joined to their statuses. If you only wanted to check what statuses one customer had, OR what customers were of any one status, you'd add a WHERE clause
Check all statuses for 1 customer:
SELECT Customer.Name, CustomerStatus.Status
FROM Customer
INNER JOIN Customers_To_Status
ON Customer.CustomerID = Customers_To_Status.Custom
INNER JOIN CustomerStatus
ON CustomerStatus.StatusID = Customers_To_Status.Status
WHERE Customer.CustomerID = 1
Check all customers of 1 status type:
SELECT Customer.Name, CustomerStatus.Status
FROM Customer
INNER JOIN Customers_To_Status
ON Customer.CustomerID = Customers_To_Status.Custom
INNER JOIN CustomerStatus
ON CustomerStatus.StatusID = Customers_To_Status.Status
WHERE CustomerStatus.StatusID = 1 <!--- e.g. this gets you all the Agents --->
Check all statuses for 1 customer:
SELECT Customer.Name, CustomerStatus.Status
FROM Customer
INNER JOIN Customers_To_Status
ON Customer.CustomerID = Customers_To_Status.Custom
INNER JOIN CustomerStatus
ON CustomerStatus.StatusID = Customers_To_Status.Status
WHERE Customer.CustomerID = 1
Check all customers of 1 status type:
SELECT Customer.Name, CustomerStatus.Status
FROM Customer
INNER JOIN Customers_To_Status
ON Customer.CustomerID = Customers_To_Status.Custom
INNER JOIN CustomerStatus
ON CustomerStatus.StatusID = Customers_To_Status.Status
WHERE CustomerStatus.StatusID = 1 <!--- e.g. this gets you all the Agents --->
ASKER
duncancumming: Wow you never cease to amaze me with you explainations... thank you! I'm more concerned with your idea of creating the other table and how to insert the data into the table.
Example if there is a multiple select input form and the user can select the multiple contact types for a contact... the form action would have to do multiple inserts into the Customers_To_Status table based upon the differnt types. At least this is the way I envision it. I get what you are saying I'm just having a tough time seeing how I could implement it into what I currently have in place.
Example if there is a multiple select input form and the user can select the multiple contact types for a contact... the form action would have to do multiple inserts into the Customers_To_Status table based upon the differnt types. At least this is the way I envision it. I get what you are saying I'm just having a tough time seeing how I could implement it into what I currently have in place.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
OK cool I did not know you could do it like that (inserting that is). Why would you need to delete? Would this be for an update? If you are inserting a contact into the database why would you delete if it's not in there? Just curious I don't get that part
yeah sorry, I was thinking of when doing an update. There are other ways of dealing with it too, but doing the delete is probably the simplest.
ASKER
thanks duncancumming