1

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) :

enter image description here

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

Community
  • 1
  • 1
sdespont
  • 13,915
  • 9
  • 56
  • 97
  • 1
    I don't think there is much to improve. Well, technically, if an actor always points to either a company or a person, the type can be deduced from checking which one is filled in, so you could eliminate actor_type altogether. – GolezTrol Apr 04 '15 at 08:19
  • 3
    Thank you for introducing me to yUML.me ! – eggyal Apr 04 '15 at 09:35
  • Are there additional fields in the `actor_type` table? If not, merge it with the `actor` table. – DanMan Apr 04 '15 at 11:47
  • 2
    The common term is "Party" over "actor". Search party model, table inheritance, database subtyping. STI is simpler and faster but uses nulls. Some dbs don't require extra storage for nulls – Neil McGuigan Apr 04 '15 at 22:16

3 Answers3

2

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.
Mike Curry
  • 1,609
  • 1
  • 9
  • 12
2

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:

enter image description here

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • 1
    It may be harder to control that you don't have a company *and* a person pointing to the same actor (or neither). Still, I think this method results in a better structure. That is, no needless companyid and personid, and more obvious that company and person are extensions to actor (because they share the same key) – GolezTrol Apr 04 '15 at 10:24
  • @sqlvogel Sorry, I made a mistake in my digram, an actor can be referenced many times in the table ´one_table´ or others tables. In that case, your design is not working because I would have one entry in the actor table every times I would need to point ´person_id´ 1 for example. – sdespont Apr 04 '15 at 10:42
  • @sdespont, I don't think that changes my suggestion. It just means the relationship between actor and one_table becomes one-to-many instead of one-to-one. – nvogel Apr 04 '15 at 11:00
  • @sqlvogel my mistake, you are absolutely right! But it means that when a person or a company is created, it is necessary to create an entry in the actor table first. So if I well understand, the number of actor table rows will be person rows count + company rows count. Am I right? – sdespont Apr 04 '15 at 14:51
  • Yes, that's right. The same number of actors (person+company) as would be required by your original design as well. – nvogel Apr 04 '15 at 15:19
1

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".

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • Thanks for pointing the right tagged to use. I was sure that this problem was a common one, but without the rights keywords, it is hard to find some help. – sdespont Apr 04 '15 at 14:57
  • Yeh. The problem here is that few people know the buzzwords without also knowing the answers. – Walter Mitty Apr 05 '15 at 13:17