For example, in my app I have some class called Contact:
case class Contact(name: String, email: String, phone: String)
And several other classes have one-to-many relation to that contact (for example, there are Companies and Houses).
Currently, I have database table for Contacts set up as something like this:
CREATE TABLE `Contacts` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `entityId` bigint(20) NOT NULL, `entityType` tinyint(4) NOT NULL, `name` varchar(300), `phone` varchar(300), `email` varchar(300), PRIMARY KEY (`id`) );
entityType is storing the "code" that determines the entity type this contact belongs to (1 for Company, 2 for House, etc), and
entityId points to the row in Companies or Houses table. Companies and Houses tables are defined as follows:
CREATE TABLE `Companies` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `company_name` varchar(300) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `Houses` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `address` varchar(300) DEFAULT NULL, PRIMARY KEY (`id`) );
So, when I need to get all contacts for some Company (which has id = 1234 in this example), I execute the following query:
select * from Contacts c where c.entityType = 1 and c.entityId = 1234;
Now, I understand that this is awful - at the very least, I can't create a foreign key on entityId, thus I can't ensure that my data is consistent.
The alternative will be to create a separate Contacts table for each entity type (CompanyContacts, HouseContacts, etc) but that will result in lots of duplication in both database and application code.
Is there a better way to do this?