CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-databaseerd
MrD
MrD

Reputation: 2481

ERD design pro and contra

I have one question regarding database design.

Here is the first example: Database schema

User may have a multiple Websites, and user can request specific resource for every of his websites. All requests are saved in RequestForResource table.

Now, if I want to see the name of an user who requested a resource, I have to join tables RequestForResource Website and table User.

To avoid this, I can make foreign key between RequestForResource and User table like it is demonstrated here:

Schema 2

Now, in order to get an user name, I have to join table RequestForResource and table User which is probably easier for SQL server, but at the other hand I have one foreign key more.

Which approach is better and (or) faster and why?

Upvotes: 0

Views: 55

Answers (1)

Pred
Pred

Reputation: 9042

You can always duplicate information to gain execution speed. This is called: denormalisation. Yes, it will probably speed up the queries by lowering the required count of index seeks.

BUT

You have to write your code to make sure, that the data is consistent: With the second design it is possible, to insert Website.User_idUser and a RequestForResource.User_idUser with different IDs for the same site! According to the design this is valid (but probably this will not satisfy your business rules).

Consider to update the foreign key constraint (or add a second one) which refers only to the Website table (User_idUser, Website_idWebsite) and remove the User-RequestForResource one.

Also consider to build a view to query your data with all the required info (probably with a clustered index).

Upvotes: 1

Related Questions