zq29 Posted October 7, 2009 Share Posted October 7, 2009 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... Quote Link to comment https://forums.phpfreaks.com/topic/176835-variable-foreign-keys/ Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.