Mysql – Table attribute for sorting

database-designMySQL

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

Best Answer

Without seeing the table schema, I see two options:

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.
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.

Related Solutions

MySQL: Why is auto_increment limited to just primary keys

Why would you want to have an auto_increment column that is not the primary key?

If you want a column to be an auto_increment, by definition, you are not storing meaningful data in that column. The only case where storing non-meaningful information makes sense is the special case that you want to have a synthetic primary key. In that case, the lack of information is a benefit because there is no risk that someone will ever come along in the future and want to change the data because some attribute of some entity changed.

Having multiple auto_increment columns in the same table seems even odder. The two columns would have the same data-- they're being generated by the same algorithm and being populated at the same time after all. I suppose you could come up with an implementation where it is possible for them to be slightly out of sync if there were enough concurrent sessions. But I can't imagine how that would ever be useful in an application.

Sql-server – How to get the foreign and primary key details for a table in SQL Server 2008

You should become familiar with the new catalog views. The old sysobjects, syscolumns etc. have been deprecated and are only provided for backward compatibility reasons - they shouldn't be used for new development and old code should eventually transition to the new objects.

Here is a query that will get both sides of the columns involved in foreign keys:

SELECT 
        constraint_name   = OBJECT_NAME(fkc.constraint_object_id), 
        foreign_schema    = SCHEMA_NAME(child.[schema_id]), 
        foreign_table     = child.name, 
        foreign_column    = child_cols.name, 
        referenced_schema = SCHEMA_NAME(referenced.[schema_id]),
        referenced_table  = referenced.name, 
        referenced_column = referenced_cols.name
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.tables AS child
    ON fkc.parent_object_id = child.[object_id]
    INNER JOIN sys.tables AS referenced
    ON fkc.referenced_object_id = referenced.[object_id]
    INNER JOIN sys.columns AS referenced_cols
    ON fkc.parent_column_id = referenced_cols.column_id
    AND referenced_cols.[object_id] = referenced.[object_id]
    INNER JOIN sys.columns AS child_cols
    ON fkc.referenced_column_id = child_cols.column_id
    AND child_cols.[object_id] = child.[object_id]
    WHERE referenced.name = 'table name'
    -- if you want the tables that a child table references, use this instead:
    -- WHERE child.name = 'table name'
    ORDER BY fkc.parent_column_id;

And this one is much simpler, just for the tables involved:

SELECT 
    constraint_name = name, 
    foreign_schema = OBJECT_SCHEMA_NAME(parent_object_id),
    foreign_table = OBJECT_NAME(parent_object_id), 
    referenced_schema = OBJECT_SCHEMA_NAME(referenced_object_id),
    referenced_table = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'table name'
-- if you want the tables that a child table references, use this instead:
-- WHERE OBJECT_NAME(parent_object_id) = 'table name';

EDIT adding some links to documentation (these mostly fall within the SQL Server 2008 R2 hierarchy but they're roughly equivalent for SQL Server 2008 as well):

Best Answer

Related Solutions

MySQL: Why is auto_increment limited to just primary keys

Sql-server – How to get the foreign and primary key details for a table in SQL Server 2008

Related Question