database relationship
- Thread starter mattux
- Start date
Soldato
I'm not entirely sure what you're after but I'd split contacts into two tables; contacts_clients and contacts_jobs or add a new column to contacts for type (e.g, 'C' for client or 'J' for job but you lose your relationships this way).
If you want all results in one query you'd just need to use a UNION ALL to select from both your contacts_clients and contacts_job as one result set.
If you want all results in one query you'd just need to use a UNION ALL to select from both your contacts_clients and contacts_job as one result set.
I would unsplit them and do this instead:
So then you could do the following to get the contact details for a job (JobId 2 in the below example).
SELECT c.ContactName FROM Contacts AS c
INNER JOIN ContactEntityMap AS cep ON c.ContactId = cep.ContactId
WHERE cep.EntityId = 2
AND cep.EntityTypeId = 2
This also means if you wanted to add a new type of contact you would just need to add a new table like Jobs and add a new record to the EntityTypes table.
Apologies for the poor formatting, looks like the forum strips unnecessary whitespace!
Code:
Clients
--------------------------------
ClientId (pk) | ClientName
1 | Test
2 | Test2
Jobs
--------------------------------
JobId (pk) | JobName | JobDescription | ClientId (fk)
1 | TestJob | Testes | 2
2 | TestJob | Huge Testes | 2
3 | NoJob | Tiny Testes :( | 1
Contacts
--------------------------------
ContactId (pk) | ContactName
1 | Dr Nutsuck
2 | Joe Bloggs
EntityTypes
--------------------------------
EntityTypeId (pk) | EntityType
1 | Client
2 | Job
ContactEntityMap
-----------
ContactId (fk) | EntityId (fk) | EntityTypeId (fk)
1 | 2 | 1
1 | 2 | 2
2 | 3 | 2SELECT c.ContactName FROM Contacts AS c
INNER JOIN ContactEntityMap AS cep ON c.ContactId = cep.ContactId
WHERE cep.EntityId = 2
AND cep.EntityTypeId = 2
This also means if you wanted to add a new type of contact you would just need to add a new table like Jobs and add a new record to the EntityTypes table.
Apologies for the poor formatting, looks like the forum strips unnecessary whitespace!
Last edited:
^
That's what [ code ] tags are for.
The issue with that approach is that, while it's extensible, you lose the referential integrity back to the Clients and Jobs tables. That may or may not be an issue for the OP.
If I was modelling something like this I'd want to know if a client contact is semantically the same as a job contact. If they effectively mean the same thing, then you don't want to completely split the contact tables.
In that case, assuming you want to keep RI, I'd have job_contacts and client_contacts tables that just map between the jobs/clients tables and a single contacts table that has all of the contacts in it.
If they mean different things, then I'd split them up entirely to avoid any confusion.
That's what [ code ] tags are for.
The issue with that approach is that, while it's extensible, you lose the referential integrity back to the Clients and Jobs tables. That may or may not be an issue for the OP.
If I was modelling something like this I'd want to know if a client contact is semantically the same as a job contact. If they effectively mean the same thing, then you don't want to completely split the contact tables.
In that case, assuming you want to keep RI, I'd have job_contacts and client_contacts tables that just map between the jobs/clients tables and a single contacts table that has all of the contacts in it.
If they mean different things, then I'd split them up entirely to avoid any confusion.
You're absolutely right Haircut, my bad!
The only problem I can see with your approach of having 2 map tables is that you end up with loop dependency but I think it's necessary and would cause issues, as well as retaining RI.
The OP appears to have solved the problem anyway but it's interesting to bounce ideas around, thanks for pointing out the flaw in my design.
The only problem I can see with your approach of having 2 map tables is that you end up with loop dependency but I think it's necessary and would cause issues, as well as retaining RI.
The OP appears to have solved the problem anyway but it's interesting to bounce ideas around, thanks for pointing out the flaw in my design.