2

We have a MySQL database with employees, companies and addresses tables. The setup works like this: employees and companies each have an id. Since they are in two different tables, an employee can have id = 1 and the company can have id = 1. Both can have multiple addresses.

Now the address table has two columns that links it either to a company or to a employee: 

element_id  
element_type_id

element_type_id is either 1 = person or 2 = company

The whole thing is a bit more complex and there are many more tables, but that kinda explains the concept. The problem is now that we would like to start using the Entity Framework and for that we need to define relationships with foreign keys.
But that sounds pretty much impossible with the setup that we currently have, does it? Since the address table would need to be combined with the person and the company somewhow....

Any ideas?

John Woo
  • 258,903
  • 69
  • 498
  • 492
Remy
  • 12,555
  • 14
  • 64
  • 104
  • Can you change the database schema? – Gert Arnold Aug 29 '12 at 12:32
  • In theory we could. But as I said, it's a bit more complex than that. This approach has been used widely in the db and it does come in pretty handy in lots of situations. For example we have tasks. And this task could be for a company, employee, job, order or deal. It's exactly the same WebForm Control that we can just add to every element that needs tasks. – Remy Aug 29 '12 at 13:19

2 Answers2

0

You already meantioned yourself that this concept is not fitting into a relational database. It is not wrong, MySQL and all the other just don't support it. And it is not a normalized layout, so it might be regarded bad practice by database engineers.

You should think about separating the address from the relation by creating two additional tables: employee_adresses and company_addresses, each holding a relation to the company and the address or the employee and the address. This way you might get one address that is used for a company and many employees, which might be a good thing (normalized structure).

Argeman
  • 1,345
  • 8
  • 22
0

This concept is known as polymorphic associations. I once asked a question about it, because I had a similar data structure that I wanted to make referentially sound. In terms of this question your Address corresponds with the Comment table and Employee and Company with Person etc.

The answer was excellent. If you can change the schema I would certainly go for it and use that approach.

If you can't change the schema you can always subtype your Address class in your EF model and use element_type_id as the discriminator column. You would create subtypes EmployeeAddress and CompanyAddress, where Employee refers to the former and Company to the latter. But as "the whole thing is a bit more complex" I'm not sure if this will be feasible in your situation.

Community
  • 1
  • 1
Gert Arnold
  • 105,341
  • 31
  • 202
  • 291