2

I'm designing a webapp where users can add widgets to their pages. Right now I have only two types of widgets that are different in nature, so they have different attributes, but both of them are used to sell products. I'm expecting to have other widgets implemented in the future. The users have the option of adding one or both of them to their pages, and I have to keep record of what widgets the users add. Also, for each product sold, I have to generate and store a receipt of the financial transaction. Since every sale is the same regardless of which widgets it comes from, I'd rather have only one Sales table for storing them (instead of one table for each widget type, eg: Widget1Sales, Widget2Sales, etc). And since there may be other widgets developed, I prefer not to make a sparse Widget table with columns for the attributes of all widgets.

So far I came up with the following design:

Widget and products table design

So for each widget the user add to his/her page, I have to make two queries: one to retrieve the specific widget table name and another (possibly a dynamic query) to get the widget info.The same goes for the products: for each product sold for any given widget I have to make a query to retrieve the specific product table name, and another to get the product info. This arrangement seems to work, but storing table names in another table sound just plain wrong to me... Is there any other way I could implement this logic? I've considered implementing some sort of class table inheritance, but I can't seem to envision how I could go about this in that specific use case.

1

Storing table names in other (user) tables is an instance of storing structural metadata and user data side by side. This has a variety of interesting effects, and some of those effects make it attractive to designers. The down side is less apparent.

The down side is this: you end up doing a lot of data management "by hand" in the SQL that a different design might have allowed the DBMS to do for you. This makes your code harder to maintain and slower to run.

Having said that, I'll admit to having pulled this stunt several times, and the results were usually good enough so that I didn't regret the choice.

There's a second thing going on in your case. Types 1 and 2 of both widgets and products are cases of class/subclass modeling (or, if you prefer type/subtype modeling). This kind of thing is simple and straightforward in object modeling, because inheritance takes care of most of the difficulties for you. Not so in relational modeling. Relational modeling, as such, has no mechanism for inheritance. Some variants of SQL have extensions that make inheritance easier to model and to implement.

There are many case of questions here in the DBA area that boil down to the question of how to implement subclasses (or subtypes) in SQL tables. Some of those questions are grouped under this tag: . Over in StackOverflow, there are even more such questions, and there are three tags that relate to three design techniques that might help: single-table-inheritance, class-table-inheritance, and shared-primary-key.

Your design resembles a class table inheritance design, except that you use embedded table names instead of shared primary key to implement the IS-A relationships between subclasses and classes. You might want to explore using shared primary key, and then creating views that collect all the data for each subclass by joining the superclass table with each subclass. I'm not sure you want to go this way. It could get awfully unwieldy once you have hundreds of different product types.

0

I have somehow the impression that you struggle with a similar problem as me...

I would suggest to review your DB model. I have the impression that it's overdesigned and not smart relational...

The foreign key relation of widget < Widget 1; widget < widget2 if you have a widget id x, this id is only in widget 1 or widget 2, not in both. at the other hand, you can make an additional foreign key in widget 1 pointing to widget_type.

OR even better: if you include the columns of widget_type into widget, in the widget table you would have already the info if it's widget 1 or widget 2, so you would know that widget id_x is of type widget 1 or widget 2 and in which table to look for it.

At the same time, Product type 1 & product type 2 are very similar. the only difference is that product type 2 has 2 more fields, so make this nullable, use 1 table product_type; the same for widget1 & widget 2, widget 2 has just one field more than widget 1 and if you go on, you suddenly see, that with less tables you can hold the same info...

Widget

id widgetType ( can be 1 or 2) widgetName widgetDescription pageID

WidgetInstance

id widgetID attribute1 attribute2 (nullable)

Product

id name description Price weight (nullable) image_url (nullable) widgetInstanceID

like this you reduce 6 tables to 3 You could also make a table widgetProperties widgetInstance 1:n widgetProperties etc

  • Thanks for your insights. The WidgetType and ProductType tables can definitely be merged into their respective main tables. As for putting the attributes of the specific widgets into one WidgetInstance table (and also for the products table) is that I'm planning to add more widgets over time, which would require migrating the schema. I may also end up with a sparse table with several null columns for each record. – mgaldieri May 12 '16 at 5:36
  • @mgaldieri: Yes, I agree, it always depends how one expects the system to grow... Therefore I pointed out in the end, that you can also externalize the varying attributes of a widget to an attribute table: Widget(Instance) 1:n widgetAttributes And ProductType 1:n ProductProperties You can then categorize these properties too, etc... Like this you can have a widget with no additional attributes and can construct one with a million attributes, without having to change the DB design... – Canelo Digital May 13 '16 at 17:00
  • The link points to a question that has been removed. – Walter Mitty Jan 2 '18 at 3:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.