I am designing a datamodel for a new project.
One of the requirements specifies that some objects can point either a person or a company.
What is the smartest way to achieve that?
I have thought about a table link "actor" like this (drawn with the excellent yUML.me BTW) :
In the actor table, according to actor_type, person_id or company_id is a foreign key on its corresponding table or is NULL. This way, when one_table wants to retrieve details about the actor, I start by checking the actor_type field and retrieve either person_id or company_id.
It is working, but I am looking for a better design.
Here is the link for editing the diagram
I suggest you make company and person subtypes that reference the same supertype (actor). Note that you don't need separate person_id, company_id because an actor must be one or other and cannot be both. Like this:
It is hard to really give an answer to this, but I'd have some notes that might be useful to you.
But they are just some notes really.
Overall this data model seems fine given what is known.
- Actor type doesn't necessarily need to be another table. It could just be a coded value on this table for example.
- As noted in other comments the actor type table is not technically required if the actor can only ever point to the two things - but I'd probably still use it as it is easier to maintain and more readable in the long term for future maintainers of the code/sql/database. i.e. the presence of the field to indicate type adds value in human readability of the code/sql and the model even if the machine doesn't need it.
- Thinking about the evolution of the database, one thing to consider, do you think it will always just be person/company or may there in the future be different types. If it is likely to be additional actor types in the future, it is worth considering now whether factoring the actor_type and id to a link table might be useful to limit the evolution of the schema for this table.
This is a classic case of classes/subclasses (or, if you prefer types/subtypes). I suggest you visit the following three tags and look over both the tagged questions and also the tag wiki you can see under "learn more".
single-table-inheritance
class-table-inheritance
shared-primary-key
This may give you all the answers you need. Or you may prefer to add one or more of these tags to your question, so as to attract more answers.
Note: the reason the word "inheritance" is used in the tags is that these techniques allow you to gain some of the benefits that are built into object environments that implement inheritance.
