1

Let's talk about an online service where goat/sheep/cattle/llama farmers would register and keep track of their animals.

One of the features of such a service is tracking parent/child relationship. However, it's not as trivial as adding a parent_id field. Here are the reasons why:

  1. There are many farmers, each with their own account; they're selling animals to each other. This means that not every parent can be entered into the system with all its properties. So there is no parent_id primary key to link to.

  2. There are two IDs, national and internal. None of them, one of them or both of them can be entered. There should be a priority in determining the animal's parent. Internal IDs have priorities. Then comes national IDs. This means that if both are specified, the parent should be determined by an internal ID.

  3. Not all animal cards are made public by their owners. This means that we cannot rely national IDs and look for parents through the whole database and fetch parents' data to display the tree.

  4. Furthermore, the National ID is a NOT a unique field among the database (but is unique for each user and species). This is made so to allow farmer1 enter national ID "ABC" even if farmer2 entered national ID "ABC" for another animal as well (e.g. by mistake).

As you can see, this all makes impossible to rely on a single parent_id column. That's where come various approaches to solve the issue, and I ask your advice.


Approach #1

Introduced the following columns:

  • mom_national_id (nullable)
  • mom_internal_id (nullable)
  • dad_national_id (nullable)
  • dad_internal_id (nullable)

Unique indexes (user_id, species_id, mom/dad_national_internal_id)

Approach #2

  • mom_id BIGINT UNSIGNED (nullable)
  • is_mom_id_national BOOL
  • dad_id BIGINT UNSIGNED (nullable)
  • is_dad_id_national BIGINT UNSIGNED (nullable)

So, here are my questions

Q1. Is either of the approaches above correct, or can you recommend a better one?

Q2. If both of them correct, which one would be better in terms of querying speeds for building parent/child trees? Does it matter at all if all is needed is just up to ~50 generations up or down?

Q3. Which indexes are needed for best performance?


Despite the solution should not be specific to database type, the service is being built with using PostgreSQL.

1

The best approach would be neither of the two options you've proposed. Instead you should create your own proprietary surrogate key using either a monotonically increasing integer or a GUID if that is how you like to do these things. This key column would be NOT NULL on your animal table (obviously).

You need to keep all of the external IDs that are provided to you (internal/national) and use these to establish the mother_id and father_id of each new animal that you add to your database according to the complex rules that you've described.

This approach allows your system to take advantage of declarative referential integrity and ANSI SQL joining while preserving the mixed bag of external IDs that are provided to you for reference/reporting etc.

  • Thanks for your advice. So, do you mean I'll need to update mother_id and father_id every time any of internal/national IDs change? And use only father/mother_id when building trees? Which makes other IDs just for user convenience, but not for the tree building. Have I understood you correctly? – Meglio Jan 17 '16 at 22:56
  • @Meglio - Changing primary keys is a sure way to find yourself with orphaned child records. Since in the real world, it's possible that unique identifiers can change, many people like to create surrogate keys which never change. You can guarantee this because you control the surrogate key. The idea is that each new animal gets your private unique ID number (or GUID, whatever), which never changes. If the external IDs change that's OK, update that record. Build your parent/child relationships with the private keys that you control. Your users don't even have to see the private keys at all. – Joel Brown Jan 17 '16 at 23:48
  • Thanks for the detailed explanation. Actually, it was not my intention to use external IDs as primary keys. The main point here is that I wanted less work to be done by users and instead more work to be done by the system. That's why I was thinking about dynamic tree building with using the complex rules described above instead of managing parent IDs. What is not clear for me is how could I not bother users if they e.g. enter external IDs incorrectly and there are two animals with the same national ID? Which animal's ID should I automatically use as father_id then? Just one of many examples – Meglio Jan 18 '16 at 2:12

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.