0

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.

Lee
  • 3,869
  • 12
  • 45
  • 65

2 Answers2

1

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.

steven
  • 4,868
  • 2
  • 28
  • 58
  • Yes, you're right. I thought about a composite key but when no attributes are candidate keys, it's not possible. Is there any real advantage of making each tuple unique with an id primary key, in this case? – Lee Jun 23 '11 at 12:49
  • The advantage of the primary key is to be able to identify each tuple. If you dont need to be able to identify for deleting or editing reasons then you dont need it for making your app running. But I would allways define a primary key to have a "clean" design. – steven Jun 24 '11 at 12:00
0

Tricky question ! I don't have a clear answer, but i think you may run into trouble if you don't have at least a unicity constraint on the whole tuple : imagine some app runs amok and tries to insert 1.000.000.000 times the same tuple in your table...

m_x
  • 12,357
  • 7
  • 46
  • 60