Complex surrogate foreign keys like PC or standalone surrogate key

The figure below shows two alternative database designs. One with a composite of two foreign keys and one with its own surrogate key. The second option will have a tricky unique constraint for the DanceGroupId and StudioId, so two of them cannot be entered. I have always used the first design, but after I constructed my classes in C # it would help repeat the use of C # code if I could place a common non-composite surrogate key on all my tables. Regarding database design, what are the downsides of using a surrogate on the table like "Reservation" instead of "composite"?

enter image description here

I'm not looking for a generic complex and surrogate debate here, its a bit different because my composite key in the first option consists of foreign keys. As far as I know, surrogates prefer natural keys due to their imprudence on business-related data. This is not a concern in this scenario and I have never seen this approach before on a "middle" table. It seems reasonable to implement it as in the second option, but I was wondering if I was missing anything.


source to share

1 answer

Possible negatives of defining a pseudo-cue when you don't need it:

  • One extra integer per line makes the strings wider, uses more disk space and memory.

  • More arguments about the mechanism are used to generate unnecessary pseudo-oc values.

  • Porting using a clustered index to improve performance. It makes sense to define the clustering index according to the most common queries. For example, if your queries most often include conditions for StudioId, then make that the first column of the clustered index. In some SQL implementations (e.g. MySQL), the primary key is always used as a clustered index. In some other SQL implementations, you can choose a different secondary unique key as the clustered index.



All Articles