I have 3 Tables, a User table, a Tracks table and a Likes table. Users can like as many tracks as they want and one track can be liked by multiple users. In my Likes table I have two foreign keys to the User and to the Tracks table.

I'm getting the data from an API, that sends it in the correct order, latest likes sent first. But there's no field like a date when it was liked on. It's only the order. Now I want to put the likes in my database in the correct order, so I made a third column in my Likes table that is named liked_order. It's an AUTO_INCREMENTED value, therefore my foreign keys and the liked_order attribute are my primary keys for this table. Unfortunately it is now possible for a user to like a track twice. If the AUTO_INCREMENT value wouldn't have to be a primary key everything would be fine.

I've ran out of ideas...

EDIT: I have to use MySQL

  • Do you write directly in Likes table? – Stoleg Nov 24 '15 at 16:26
  • Yes, when I get the data from the API, my program inserts it into the Likes table. – moritzg Nov 24 '15 at 16:27
  • Can your program assign Order or can you write it in a different table and then copy? So you can avoid using AUTOINCREMENT. And what is the problem in current design? Like order will never duplicate, so there will be no duplicate keys. – Stoleg Nov 24 '15 at 16:32
  • Yes I can programmaticaly assign the order, but the user can add new likes in the future, so after the first insert my program would have to get the newest order from the database. In my opinion this is work of the database and has nothing to do with my program. I think it's good design to leave that to the database. There will be no duplicate keys but that's the exact problem, users can like tracks how often they like. – moritzg Nov 24 '15 at 16:54

Without seeing the table schema, I see two options:

  1. Adjust the schema so that the Foreign Keys(FK) are not part of the Primary Key(PK); only the AUTO_INCREMENT column is the PK. The Foreign Keys can still be Unique Indexes to prevent multiple likes of a particular track by the same user. This won't work of course if the FKs are part of a partitioning scheme.
  2. Take out the AUTO_INCREMENT column and add a TIMESTAMP(0) DEFAULT CURRENT_TIME column. The timestamp column will take up as much space as an INT AUTO_INCREMENT column. The downside to this is all rows before adding the timestamp column would not have an order.
  • The table schema is nothing fancy, just the two tables with a n:m relation. Which option would be the better approach/Which would you choose? – moritzg Nov 24 '15 at 18:30
  • Both methods means adjusting PK, so are invasive. However, if you ever see a need to map exactly when likes happen, go with option 2 and take the hit on existing rows not being ordered. – Derek Downey Nov 24 '15 at 18:39
  • Well I have no way to know when likes happen, I just get the data from the API, so I'll go with number 1. Thank you very much for your help. – moritzg Nov 24 '15 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.