Jump to content

Recommended Posts

I wasn't totally sure how to define what it is I'm talking about, so I'll stick with "Variable foreign keys" for the time being, until someone can identify what it is!

 

Consider a scenario where a website is selling completely different types of product with different attributes. For this example, lets say we're selling tickets, packages and memberships. Each of these types of product have their own tables in the database - ticket, package and membership. For the sake of simplicity, there is also customer and purchase.

 

My dilemma surfaces when deciding how to track the purchase of any/all of these products by a user. The way I see it, I have two options. Using what I have defined as a "variable foreign key", or restructuring the database slightly.


Variable Foreign Key

This would be where I have (amongst the usual stuff) the fields type and foreign_id within the purchase table, where type would be an ENUM with the possible values T, P and M (ticket, package, membership) and foreign_id would refer to the id field within either the ticket, package or membership table. This would involve less code, logic and shorter queries, but I am concerned that this might be unconventional/frowned upon in database design.

 

Database Restructure

I haven't thought this through to conclusion, but I don't see why it shouldn't work - But this, I'd imagine, would require more work on the scripting / logic side. This would involve the creation of a further table, item, which contains the common fields of ticket, package and membership (potentially id, name, price) and a one-to-one relationship from item to those three tables to cover the uncommon attributes. The purchase table would then just refer to item(id).


So, are "variable foreign keys" a sensible design decision, would restructuring the database (my way, or a better suggested way) be the more conventional method, or is there a common solution to this problem that I'm just flat out missing? Is this just a silly post? I would have normally just gone with my "variable foreign keys" concoction, but I have recently been re-reading a bunch of resources on database design, relationships and normalization in the hope to get rid of some bad habits I might have picked up since I first learnt database design x years ago...

Link to comment
https://forums.phpfreaks.com/topic/176835-variable-foreign-keys/
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.