Postgresql – Store table names to reference other tables

database-designpostgresqlsubtypes

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:

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.

Best Answer

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: subtypes. 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.

Related Solutions

Mysql – Storing vs calculating aggregate values

It depends. Pre-calculating aggregate values places a larger load on writes, deriving them makes reads more difficult

If you are frequently accessing a derived value, pre-calculation is a valid de-normalization step. However, in this instance, I recommend using a Materialized View (a view, written to disk, linked by trigger to the parent tables). The materialized view is designed to store frequently asked but tedious-to-derive data, and is useful for high numbers of writes and low numbers of reads.

In a high-write, high-read scenario, consider having a task in the background which mimics the effects of a materialized view, but in less than real-time. This will present a "good enough" average while preserving write and read performance.

In no circumstances, should you treat the derived column like a "normal" column: make sure the data presented in the Widgets "view" is present elsewhere in the table, such that the entire tuple can be derived by whatever processes you emplace. This question is also strongly database (and database-version) specific, so I recommend performance testing of the aggregate (with appropriate indexes) against a normal-sized data set and the materialized view.

Database scheme for product tracking

Normally what you would have is something like this:

CREATE TABLE Product (
ProductID,
Name,
Description
) ;

CREATE TABLE Order ( /* Shipped products */
OrderID,
ProductID,
ShippedDate
);

CREATE TABLE User (
UserName
) ;

And here's the essential table you need. This establishes a many-to-many relationship (commonly known as an intersection or bridge table) between Orders and Users:

CREATE TABLE User_Order (
OrderID,
UserName
) ;

For every order you would insert one record into the User_Order table for every user that can track that order. So you would have something like this:

OrderID         UserName
-----------------------------
1               joe
1               jane
1               john
2               joe
3               bill
3               jane

Best Answer

Related Solutions

Mysql – Storing vs calculating aggregate values

Database scheme for product tracking

Related Question