3

Assuming in a Postgres database, you have a table called party, which can have less than 5 well-defined party_types such as 'Person' or 'Organization'.

Would you store the party_type in the party table (e.g. party_type = 'Person') or normalize it (e.g. party.party_type = 1 and party_type(id, name) = (1, 'Person'))?
And why?

Improve this question
5
  • I wouldn't normalize that unless it's for a datawarehouse with large amounts of data where the bits saved can sum up to significant amounts of space. So my answer is "depends" :) – Allan S. Hansen Feb 24 '14 at 8:06
  • I wouldn't call that normalization by the way. – ypercubeᵀᴹ Feb 24 '14 at 9:35
  • @ypercube can you please explain? – Mike81 Feb 24 '14 at 9:52
  • 3
    Adding a surrogate key column has nothing to do with normalization. – ypercubeᵀᴹ Feb 24 '14 at 9:54
  • @ypercube you mean the case when there is a FK defined on party_type? – dezso Feb 24 '14 at 12:00
7

If you are implementing party-role-relationship model or part of it, having party_type as a separate entity is very important. You may have many more tables with foreign key to party_type (for instance, to limit applicability of certain roles to particular party type[s], or to enforce relationships between different types of parties, etc). Using check constraints in any form (including enums) in such cases would hide the logic and make maintenance harder .

Improve this answer
2
  • A bit off topic, is a party_type table a good way to create two subtypes of party (as opposed to create two separate entities / tables person and organization)? – Mike81 Feb 24 '14 at 21:17
  • 2
    I guess you meant party table, not party_type which will be a small lookup. Depends on other requirements, you may store organization and person in one table ; that seems ok to me under some circumstances . – a1ex07 Feb 24 '14 at 21:57
5

I would use an enum type for that.

Improve this answer
1
  • 4
    +1 For an entity without any further attributes, an enum type is like a poor man's lookup table and may pass as "normalized". – Erwin Brandstetter Feb 24 '14 at 17:04
0

I always use a lookup table since your app may expand ("Organization" may become "Retail organization" once you add "Institutional organization" for example). A single location for the type name is great when you realize the type is slightly more specific or different than the original name would imply. Also, this is a great place to add values specific to the type like "CanHaveMultipleLogins" which VERY often get hard coded.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.