0

Right now I have three tables: person, alias, and person_alias. As suggested by the names of tables, one person can have multiple aliases and one alias can be used by many people, and hence I add a joining table person_alias during the process normalization.

However, the table alias essentially only contains one column (if I do not choose to use numeric surrogate key). In addition, let's say the table person also contains a bunch of other useful information that is irrelevant to the question here.

I am not a bit stuck at justifying setting up the entity relationship map this way. What is the problem that I delete the table alias all together? Or do I misunderstand many-to-many relationship/normalization?

Improve this question
1
  • 1
    The alias in this scenario is not an entity. It doesn't have any attribute, and even if it did, these attributes must be shared among the persons that use the same alias to justify being an entity. In that sense alias as a table is not justified.
    – Daniel Vérité
    Jan 7, 2019 at 14:12

1 Answer 1

Reset to default
0

The alias table will save space because you do not repeat duplicated aliases in the person_alias table.

If in most of the cases aliases are unique, then this won't matter much, but if you have a lot of duplicated aliases (say every second person uses 'Zaphod Beeblebrox' as a alias) then this could save a lot of space.

However, if you allow a single person to rename their alias without affecting all other (duplicated) aliases, then removing the alias table completely could makes sense.

You could de-normalize further and store all aliases for a person in an array column in the person table removing the person_alias table as well. But in that case it will be quite complicated to define a unique index that ensures that every alias is unique for that person. If you (almost) always retrieve all aliases for a person together with that person that might be slightly more efficient.

But if you want to ensure uniqueness of the aliases per person in the database, stick with the person_alias table.

Improve this answer
2
  • Yes I am assuming all aliases in the alias table will be unique. Keeping the table alias will only save space under the assumption that "storing a varied length of string is averagely more expensive than storing an integer", right? If we assume, that all aliases are two-character string, is there still reason to keep the table alias?
    – ark
    Jan 7, 2019 at 13:20
  • @kumom: if all aliases are unique, and your aliases are indeed only two character string, then yes the alias table won't help much.
    – user1822
    Jan 7, 2019 at 13:28

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.