Composite PK and duplicate data

[Minklet]

Hello, I have a database schema done by someone else that I am switching from sqlserver to mysql.

 

Quite a lot of tables use composite primary keys, often they are made up of a few foreign keys and another column, sometimes just foreign keys and a date created column.

 

My question is, what is the point? Because when this table is referenced in another as a foreign key you are of course forced to reference all the primary keys and are just creating unnecessary duplication of data. Surely it is far more efficient for storage and also for your queries to have a unique identifier as the primary key?

 

I can see the point in some tables, like status relationship tables or something, where the table is not referenced by another. But if it is referenced then it is forcing you to add extraneous columns and data

 

 

For example

 

Table - Room

{

BuildingId (references building Id in building table) - PK

Room Id - PK

Room Type (references type_id in type table) - FK

}

 

This table is referenced my module occurrence and a few others

Module Occurrence Set

{

Module Occurrence Id - PK

BuildingId (references building id in room) - FK

RoomId (references room id in room) - FK

}

 

Now the tables are both more complex than that but this is a basic example.

 

As you can see BuildingId is referenced when it doesn't need to be, because it is a primary key in Room. If I removed the the primary key from buildingId and added a unique identifier as the primary key, it is more efficient right?

 

[ignace]

The example is completely ripped out of context, and only half definitions are given. We need to see some real CREATE TABLE syntax and data to make a judgment.