0

This is a slightly complicated setup but please bear with.

Basically I am creating a 'dress up' game. A user has many clothes and clothes can be assigned to many users so I have created (I think you call it) a pivot table user_clothes to represent this.

But clothes have a type for example 'dress', 'shoes' etc. I only want a user to be able to have one item per layer and want the database to reject an attempt to bypass this. Is there a way I can create a unique combined key on user_clothes that only allows one item per layer based on my database architecture?

I have four tables: users, user_clothes, clothes, clothes_type

users

  • id (PK)

clothes_type

  • id (PK)

clothes

  • id (PK)
  • clothes_type_id (FK: clothes_type.id)

user_clothes

  • id (PK)
  • user_id (FK: user.id)
  • clothes_id (FK: clothes.id)
James Stott
  • 2,954
  • 2
  • 14
  • 15
  • 1
    This is typically called a "compound" key. You can see an example here: http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql – Jonathan Kuhn Dec 19 '14 at 00:03
  • Oops, I was wrong. That would work if the slot was also in the user_clothes table, but it won't check that layer/slot is unique across multiple tables. You would have to check something like that in code or use a "before insert" trigger to check/validate and "rollback" if there is an issue. – Jonathan Kuhn Dec 19 '14 at 00:06
  • You could add (duplicate) the clothes_type_id field in to the user_clothes table so then you can do a unique index on (user_id, clothes_type_id). But then you have redundant data meaning the data could get in to an inconsistent state. – Phil Dec 19 '14 at 00:12
  • @JonathanKuhn thanks for the response - that sounds a bit over my head haha. Is there a way to create a "ghost" column of the `clothes_type` `id` in the `user_clothes` table because each item in `clothes` will have a single unique `clothes_id` then I could create a compound key with that? – James Stott Dec 19 '14 at 00:14
  • @Phil_1984_ ahh thanks for that! I was thinking of doing something like that. Is there not a way to, instead of duplicating the clothes_type_id, make it just a reference to the clothes_type_id in the clothes table? – James Stott Dec 19 '14 at 00:16
  • It's not uncommon to manage this kind of constraint in application code. Note that `id` on your pivot table appears redundant; you have a perfectly adequate natural key, so require no surrogate. – Strawberry Dec 19 '14 at 00:25
  • @Strawberry thanks for that advice. I think you're right - as someone relatively new to development I sometimes find knowing where to place responsibility a bit difficult. I think I'm probably over-relying on the DB. – James Stott Dec 19 '14 at 00:28
  • Well, you're asking yourself the right kinds of questions. That's half the battle. – Strawberry Dec 19 '14 at 00:30
  • I second adding clothes_type_id to the user_clothes table, making clothes_type_id and user_id a compound primary key. But contstraints on the database should be seen as a last resort -- I'd make sure to design the user interface of your app to only allow one article of clothing per layer. I don't think that's necessarily redundant -- if the data in that table (clothes) pertains to "the key, the whole key, and nothing but the key," then you have third normal form. – trpt4him Dec 19 '14 at 00:31
  • @JamesStott I am not aware of any sql which allows that kind of constraint mechanism. It is an index afterall, so you need to have the fields there in the table data. Like everyone says, it is better to keep the database simple and spend more time making sure that your PHP/SQL code cannot add a 2nd item of clothing of the same type to a user. You could do a select first to check and then insert. I think you can also use innodb trasactions to be thread safe. Web interfaces are important but should be considered hackable, as browsers have developer tools built in nowadays. – Phil Dec 19 '14 at 00:48
  • IMO, I would handle this in app code. You could easily query for the new type and query this: `SELECT COUNT(*) FROM user_clothes WHERE user_id = :user_id AND clothes_id IN (SELECT id FROM clothes WHERE clothes_type_id = :clothes_type)` which would get you a count of items the user has of that type. Another option would be just to delete everything from that slot and insert a new row. `DELETE FROM user_clothes WHERE user_id = :user_id AND clothes_id IN (SELECT id FROM clothes WHERE clothes_type_id = :clothes_type)` and then just insert a new row with the new item. Errors fix themselves. – Jonathan Kuhn Dec 19 '14 at 00:52
  • @trpt4him I think Strawberry meant redundant in the "useless" sense of the word rather than the database term. But, in my experience, it doesnt hurt and it's quite useful to keep the id there so you can refer to each relationship easily using a single integer. I always have an id on all my tables for this reason. – Phil Dec 19 '14 at 00:57

0 Answers0