Search code examples
schemarelational-model

Relational database theory and keys

I'm designing a schema to hold player data within a browser based game.

I have three relations. Two of them have at least two candidate keys, however the third has only three attributes: {playerId, message, date}

This relation will hold no unique rows as there is a 1..1:0..* relationship, meaning there can be any number of news tuples for each player. I don't need to be able to uniquely identify any tuple and none of the attributes can actually be a candidate, anyway.

My question is: I understand the relational model states there cannot be duplicate tuples and each relation must have a key. My schema above contradicts both of those constraints but works for my purpose. I know I could simply add an index attribute (like an ID) that is unique, but that seems unnecessary. Am I missing something?

Thanks for your time.

Solution

  • I think what you are missing is a composite primary key.

    In your case if you are save to get no dublicate entries you want to use a composite primary key.

    But think about the same player sends the same message at the same date.... In this case you will have a conflict with a composite primary key. A virtual unique id as primary key is a saver way.