I have a table called Contact that represents a real-life contact - so has a name, address, phone number, etc. - that could be either a Person or a Company. They obviously have a different attributes, like a person has a date of birth for example.
How do I structure this in a database?
I tried it like this.. Contact has fields id, displayName, numViews, type and entityId (fk), and added two more tables to represent a Person and a Company. So if the contact is a Person then type = 'Person' and entityId matches the person's id in its table. I couldn't figure out how to select all contacts with an upcoming birthday though. Note: I know the sql for upcoming birthday - so I can do this on the Person table - it's the joining I'm stuck on. I seem to need to join on a table where the name of the table is in my type column!
Am I overlooking something simple? Perhaps I have the structure wrong?
With your current table structure, something like this will work:
Select
<whatever fields>
FROM Person
INNER JOIN Contact
ON Contact.type = 'Person'
AND Contact.entityId = Person.id
Assuming you have a birthDate field in Person, you can also put in a where clause similar to this:
WHERE DATEDIFF(Person.birthdate, NOW()) BETWEEN 1 AND 30