Death By Penguin Death By Penguin - 3 months ago 7
MySQL Question

DB Design - generic relationships? (relation_id, relation_table)

I have recently joined a project that has a points-system. You can earn points through making a purchase, but you can also make points by redeeming a code.

Each time a set of points are given, a row gets created in the a 'points' table, tracking the user that has been given these points, the time, how many, etc.

We're also tracking how the points were given. i.e. Code or purchase?

Currently the database is structured as (cut down for my question):

id | relation_id | relation_table | user_id
--------------------------------------------
30 | 42400 | coupon | 1
31 | 39812 | payment | 1


The only name I can think to call this is a "generic relationship", but I've never seen anything like this.

As only one relationship will be active at a time, I feel it's somewhat acceptable.

I do think it's strange, and I'm not sure I would've done this.

Is there a better option for this database design?

Is the better option to just have the following.

id | payment_id | coupon_id | user_id


Within programming, I can understand why the former is easier to work with, but it just doesn't sit well with me.

Thanks

EDIT:

The
id
would be the Primary Key & AI and
user_id
would be a foreign key to reference to a users table, tracking which user owns which coin entry.

Answer

I don't know what id signifies in your table since you already have user_id, is it a foreign key?

Skipping my above question, I would suggest go with the former one.

user_id, relation_id, relation_type, id, points
1          2345         coupon        3    56
6          789          payment       96   78

Because in the later one, you cannot track the relation_type, this becomes more important when you want to see why specific user has given x number of points etc.

We're also tracking how the points were given. i.e. Code or purchase?

So, if you really want to track points you must know the type code or purchase.

As only one relationship will be active at a time, I feel it's somewhat acceptable. I do think it's strange, and I'm not sure I would've done this. Is there a better option for this database design?

Its a kind of boolean (purchase(0) or coupon(1)), yes onnly one relationship will be active at a time. But thats completely fine. You would have to create two different puchase tracker table and coupon tracker, which looks strange for me. This also violates normalization rules.

Comments