-3

my SQL teacher doesnt like identifying relations, he prefers to create a unique ID for each table

what is the benefit?

using a child-parent relation would perform better

philipxy
  • 14,867
  • 6
  • 39
  • 83
Fabio Figueiredo
  • 1
  • Guess it depends on the tables involved and their meaning? – Robert Aug 01 '23 at 15:22
  • There are certain types of database design approaches where you avoid FK relations. For example, Datawarehouse Fact tables. That is discussed, in some part, with the [answers here](https://stackoverflow.com/questions/2690818/is-it-good-practice-to-have-foreign-keys-in-a-datawarehouse-relationships) – devlin carnate Aug 01 '23 at 15:24
  • 1
    Check also the answers on https://stackoverflow.com/questions/762937/whats-the-difference-between-identifying-and-non-identifying-relationships – Robert Aug 01 '23 at 15:25
  • I think it boils down to two things. Do you like working with compound keys and is it possible that a child could change parent? – JonSG Aug 01 '23 at 15:32
  • Why must I identify a FK as "[non-]identifying"? Can't I just build the tables and FKs as needed? – Rick James Aug 01 '23 at 18:53

1 Answers1

2

he prefers to creat an unique ID for each table

I'm guessing you mean each record, not each table.

The critical issue for me is natural vs. surrogate keys.

can anyone clarify this for me?

Yes, your SQL teacher can explain his/her preference.

Personally I prefer natural keys over surrogate keys. If data already has a unique identifier then adding a surrogate key means you potentially have 2 versions of the truth, and you also need to do a join to resolve information about related entities - and that is expensive. There's also locking issues if you try to scale this to large systems (even before clustering).

However...

  1. Not all data has a natural key
  2. although MyISAM supports use of auto_increment to generate a suffix to a key (hence you can use it to create {a,1}, {a,2}, {b,1}, {c,1}, {c,2}....) this is not a common feature in DBMS
  3. queries can become diffcult to read when you have a join on more than ~5 attributes
  4. It's a lot easier when writing HTML to have a single parameter identifying a unique record than a tuple
symcbean
  • 47,736
  • 6
  • 59
  • 94
  • Also: inserts are efficient when you can always know new records will go to the end of the last page, and updates are efficient when you know records will never change part of a key that might mean it needs to move to a different page. That said, databases using surrogate keys will also tend to have other indexes that DO need those changes. One other benefit: JOINs are more efficient when they're only doing a comparison on the single (often integer) key. – Joel Coehoorn Aug 01 '23 at 16:01