6

When thinking of an application. Let us say an applications user details.

Let us say the application allows a user to easily edit their profile.

For a field like gender, which in most cases will be marked M F or other. Should those values be stored in a reference table for example.

And my over arching question is, when do you start making your application almost too involved with a schema like a Star schema that ends up not being as helpful for quick transactions?

I just worry as you start breaking everything into facts and dimensions, we end up with a reporting database that an application may not work as efficiently with.

5
  • 2
    I could swear I had seen a similar question some time ago on this site, but I have trouble to find it.
    – Doc Brown
    Aug 26, 2020 at 15:12
  • 1
    Maybe this one? It's a little older, but I think it might deal with the same thing: How should I represent an enumerated type in a relational database? Aug 26, 2020 at 15:35
  • @GregBurghardt: the question was roughly going into the same direction, and the answer may apply here, but what is missing in that older Q&A is the decision whether / why a separate table should be considered (or not).
    – Doc Brown
    Aug 26, 2020 at 16:20
  • 1
    star schema is not for a production application - but that isn't the same as using a reference (or "domain") table for "enumeration" values in your production schema. You can have (and use effectively) domain tables for things like ... oh, "designated market area", or "stock availability state", or "tax schedule" in a standard production transactional database ("orders" or "shipments" or "inventory" or whatever).
    – davidbak
    Aug 26, 2020 at 17:48
  • I personally like using an Enum data type. Of course this might not be an option in all DBMS
    – apokryfos
    Aug 26, 2020 at 17:55

2 Answers 2

3

It’s all a question of intent and balance. Up to you to make your decision.

If you put Gender values in a reference table, you have the following benefits:

  • easy customizing of values;
  • easy localisation of your code into other languages;
  • relatively easy extension of possible values;
  • uniformisation/generericity of the queries and presentation: you use the same approach for every reference table.

Moreover, if Gender is only descriptive and without impact on the app behavior, you’ll not have to worry about the possible values anymore, and not even need an enum, getting a very flexible system. On the other hand, if you have specific behavior for some values, you’ll have only a partial flexibility.

If you do not put Gender in a reference table, you have the following benefits:

  • better control on the allowed values and related behavior,
  • a couple of bytes of reference table saved (but at the size of a db, this will make no difference)
  • increased performance, avoiding a join (this applies for NoSql databases where the join may require an extra fetch; this is not an argument for an RDBMS, where the optimizer would anyway find out the reference character and cache the data for making the join without any significant overhead)
  • increased flexibility for the presentation, when the displayed value can be generated (especially if your language allows associated values like swift)

In my own experience the first approach proved very useful in simplifying the code and code reuse. But I can imagine that the second approach could outweigh these advantages in some circumstances (especially in an nosql context).

I prefer not to answer the more general question about the star schema, because it depends a lot of the underlying dbms, but also on access patterns, and moreover the writing patterns; moreover the star with non reference tables can be unavoidable depending on the requirements.

5
  • Thanks for the response. Would I be able to make such a statement like if the lookup or reference table tends to never change, then it makes sense? Versus if it regularly changes whether daily monthly or yearly than maybe a separate lookup table might be more of a negative?
    – MZawg
    Aug 26, 2020 at 17:34
  • @MZawg If the values changes often, it’s indeed one argument to go for the reference table. However, my statement remains true even if the lookup values change rarely.
    – Christophe
    Aug 26, 2020 at 19:13
  • @MZawg whether a reference table changes doesn't really matter. Refence tables make it easier to do analysis and model relations. The goal is to only model relations that you really care about, you could make a name reference table if you really wanted, but it generally brings you no analytical value.
    – Ryathal
    Aug 26, 2020 at 19:15
  • @Ryathal I marked Christopher’s answer. But I guess that’s what created this question. When you say makes is easier for analysis and model relations, all I can think about is reporting. Whereas, for example a POS system that is just transacting orders, If say we captured gender at each order (lol), why not just write this in the same row we wrote the order? Where is the benefit of giving it an ID of which to then lookup in another table? In a reporting sense, I get it. In an application sense, I feel like there’s times where lookups don’t make sense.
    – MZawg
    Aug 26, 2020 at 19:19
  • 1
    Also, Christophe, I want to make it clear your answer is ideal because of the two sided approach. Pros and cons make for a great answer if you ask me.
    – MZawg
    Aug 26, 2020 at 19:20
1

I lean towards reference tables in a relational database for one very simple reason: The "C" in ACID.

Consistency is beneficial, not just for programmers, but for users as well. I've worked in databases where fields like this were just text. The user interface displayed a dropdown or a group of radio buttons, making it appear like an enum, but at the data level it was just open-ended text. As time went on, the list of values changed, but old records were not updated. People would build reports and ad-hoc database queries expecting to find a record that gets accidentally filtered out because it was an old record with a value that was no longer presented on the user interface. This can be annoying, up to and including a fatal error in application code if proper defensive programming does not exist in the code.

References tables should include at least 7 columns:

  • Primary key (string or int, but I prefer string in this case)
  • Date this record became a valid choice
  • Date this record was deprecated
  • Who created the record
  • When it was created
  • Who last updated the record
  • When it was last updated

An optional "description" column might be a good way to record why the record was created in the first place.

Foreign keys from other tables back to the reference table ensure your enum in the application tier has a valid representation in the data tier. The begin and end dates for each record give a clear indication about whether each of those enum values are currently in use, or whether they have been deprecated. This helps when creating reports or ad-hoc SQL queries, because you know you have a record in a table with some sort of additional information about the business use for that record.

Remember that things change. Male and Female seem like pretty rock solid concepts, but social norms change. What used to be viewed as a binary choice is expanding in some countries and cultures. New values could get added. Old values can be deprecated. A reference table gives you a way to restrict your current choices, as well as keep an historical record of past choices that used to be valid, but are no longer valid.


Addendum: To make gender an even more confusing concept, each individual person might identify with multiple genders at once. Medical fields need to know which gender you were born with, because it can make a difference in medical care. Other use cases might just need to know a preference.

See Is there an industry standard for gender model other than male and female?

1
  • I appreciate very much your answer, and especially your comments about changing norms: I must say that most of the closed enum lists that I have encountered did not stand time. Personally I prefer the reference table approach. However, while the flexibility comes at no cost with an rdbms, in the nosql context the reference lookup comes at an extra cost (see docs.mongodb.com/manual/tutorial/… or the need of redundant embedding). This is why I wanted to keep my answer mire objective and balanced.
    – Christophe
    Aug 26, 2020 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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