For instance to produce a hierarchy of groups you utilize a column 'parent_id', which points to a different category within the same table.
If this should be considered a foreign key? What can the dis/advantages be?
Yes. Guarantees you don't come with an orphan (entry without any parent), and based on usage, should you define a cascading down remove, whenever a parent is erased, its children may also be erased.
Disadvantage will be a slight performance hit as with every other foreign key.
Yes, you need to. For those who have a characteristic inside a relation of database that can serve as the main key of some other relation within the same database you need to turn it into a FK.
You'll benefit from the advantages connected to foreign secrets:
- Presuming the correct style of the associations, foreign key constraints allow it to be harder for any programmer introducing an inconsistency in to the database.
- Centralizing the checking of those constraints through the database server causes it to be unnecessary to do these inspections around the application side. This removes the chance that different programs might not check constraints in the same manner.
- Using cascading down updates and removes can simplify the applying code.
- Correctly designed foreign key rules help with recording associations between tables.
- Should you define Foreign Secrets, it is sometimes harder to do bulk procedures.
- Maybe it suggests more disk usage along with a slight performance hit.
You actually should.
Advantages (for any foreign key):
- Guarantees that parent_id references a genuine row within the tabke
- Prevents accidental deletion of the parent which has children, or guarantees the remove cascades to remove the kids also
- Provides information the optimiser may use
I can not think about any real disadvantages.
Yes, you need to turn it into a foreign key.
The advantages is a better data model with less redundancy.