This question already has an answer here:
I have an attributes table, where I want a unique name for each attribute in order to prevent confusion between potentially similar attributes.
In terms of the table design, the way I see it, there are at least two realistic options.
- Have an autoincrement int PK, with an additional unique-constraint placed on the column that holds the name data.
- Drop the integer autoincrementing PK, and just use the name as the primary key, it will by definition then have a unique constraint.
This attributes table will be joined all round the place, like say many thousands of products each having one or more attributes associated with them.
I am wondering if there is any disadvantage to #2 over #1?, like the indexing costs of adding new attributes down the path, or, are joins slower. What is the best practice?
I suspect #1 is the best option from performance, but #2 has advantages for readability of the data (ie
pk:product_weight is more intuitive than
Database is MySql, and, I am writing code using a SqlAlchemy/Python ORM abstraction layer, with migrations etc...