I am very new to data modeling, and according to Microsoft's Entity Framework, tables without primary keys are not allowed and apparently a bad idea. I am trying to figure out why this is a bad idea, and how to fix my model so that I do not have this hole.
I have 4 tables in my current model: User, City, HelloCity, and RateCity. It is modeled as shown in the picture. The idea is that many users can visit many cities, and a user can only rate a city once, but they can greet a city many times. For this reason, I did not have a PK in HelloCity table.
Any insight as to how I can change this to comply with best practices, and why this is against best practices to begin with?
This response is mainly opinion/experience-based, so I'll list a few reasons that come to mind. Note that this is not exhaustive.
Here're some reasons why you should use primary keys (PKs):
TableB.table_a_id = 5then, you're guaranteed to have a row with
id = 5in
In my opinion, not having a PK might be legal (i.e. the RDBMS will let you), but it's not moral (i.e. you shouldn't do it). I think you'd need to have extraordinarily good/powerful reasons to argue for not using a PK in your DB tables (and I'd still find them debatable), but your current level of experience is not yet enough for that (you say you're "new to data modeling").
There're more reasons, but I hope this gives you enough to work through it.
As far as your
M:M relations go, you need to create associative tables and you can create a composite PK in it, that PK being a combination of the 2 PKs of the other 2 tables.
In other words, if there's a
M:M relation between tables
B, then we create a table
C that has a
1:M relation to with both tables
B. "Graphically", it'd look similar to:
+---+ +---+ +---+ | A |----| C |----| B | +---+ +---+ +---+
C table PK somewhat like this:
+-----+ | C | +-----+ | id | <-- C.id = A.id + B.id (i.e. combined/concatenated, not addition!) +-----+