4

Before making substantial changes to the content, functionality and design of my website (I'll be refactoring everything, almost a complete rewrite), I'm setting up a new PostgreSQL database to hold all the data previously in a MySQL database. I also have a heavily-customized WordPress blog that I'm going to move over to this website, effectively merging the two sites. Apart from the front-facing content for the website, there is also a lot of other information that I store in the database for my own use, such as client information, sales history, invoices, event income, etc. I'm taking a lot of time thinking things through carefully and trying to re-design the database properly with both data integrity and performance in mind.

The front-facing website will have many different "content" types: pages, events/gigs, multimedia samples, sheet music/charts (some of which will be available for sale in PDF format on the site), blog articles, etc.

One of the changes I've made is to the way I store venues/places. I used to have a "venues" table that referenced a "venue type" table as well as a "cities" table, which referenced a "states/provinces" table, which referenced a "countries" table. Most of this information was used by the "events", but then I added client information which needed to reference the cities table. Further still, all of my blog articles are geotagged, so they'll need to reference these places too. Since all the tables had a very similar schema, what I've done is create a new "places" table with "id" and "parent_id" columns so that places can be stored in a hierarchal fashion. I also have a "places_type" table so that I can enforce that the parent_id for each row has to be of a higher hierarchal type than the place itself. (So for example, a city can have a state or country as its parent type, but a city can't have a restaurant as its parent type.)

In doing this, I've created a lot of other tables with the exact same schema (id, parent_id, name) to hold "types" of other content in a hierarchy. Genres, payment methods, chart types, event types, media types, invoice types, etc.

Since all the tables have the exact same schema, would it be better to combine all these tables into a single "types" table and add a "category" field to the table?

I think it would be much easier to manage a single "types" table than eleven similar tables. I've calculated that this "types" table would hold only about 60 rows or so, for now.

I'm wondering what would be the best practice for this sort of thing in terms of database design? The data will be retrieved far more frequently than it will be updated/added.

At the same time, since this one "types" table would hold all the types for everything, it will need to be joined (via a view) many times to many tables. For example, to retrieve event information, I already join an "events" table to the "places", "artists" and "media" tables, but would also need to join these tables to the "types" table to get event types, place types, artist types and media types. So would it be best to keep the type tables separate, or combine them into one? Why?

  • This is one of those "no single simple correct answer" things. It depends a fair bit on what your requirements are. From your description it sounds like the "types" approach is probably cleaner than lots of tables with similar schema, and a join against a small table is generally very cheap if you're doing any other joins already. OTOH, sometimes performance requirements lead to necessary denormalization. I'd start with your "types" approach and adjust if you have issues down the track. – Craig Ringer May 30 '14 at 1:37
  • @DM , sounds like OTLT(One true lookup table) pattern but should use based on your requirement. Also, the major drawbacks are can't have foreign key relationships and having more hit to the same table leading to a contention. For more read, red-gate.com/simple-talk/sql/t-sql-programming/… – Biju jose Oct 10 '17 at 5:51
3

I would go for separate tables for each type. With so few rows performance will not be a consideration either way. Individual tables will only have a page or two each. Similarly for the combined table.

Either way you will have to join a "type" table to complete your views. Inserts are infrequent at this level of abstraction so locking is unlikely to be an issue either way.

For me, though, having different tables keeps logically distinct items separated, which is a cleaner design.

There will be many different tables which will have a relationship to a type table of some sort. If there are many different type tables the foreign key relationships are self-documenting and enforcable through DRI. With one combined table it is difficult to use DRI to say "Venue.TypeID comes from Type.TypeID but only if the Type.Category = 'V'", for example. The complementary side of this relationship - "Type.TypeID with Type.Category = 'V' can only be used in the Venue table" - is also not supported in any RDBMS's DRI that I know of.

  • I was planning on using a custom function in a check constraint to make sure that only valid "types" could be used in each of the subtables. Not DRI, but at least the integrity of data would still be preserved. I'm starting to think that having many separate tables may be a saner/cleaner approach after all… – DM. May 31 '14 at 11:59
  • Scalar functions can be extremely slow. Test this at your anticipated scale before commiting to this approach. – Michael Green Jun 1 '14 at 10:25
0

The table layout of your Database Depends on how granular the information you want to store is. I suggest you use an entity diagram software package to layout your new database in theory before you fill it with live data, and then realize you "oopsed" and forgot a column or table. At some point if you want some piece of information you don't have, plan for it now, even if it's empty at this particular time. On the other hand, collapsing tables is feasible if you're willing to trade the granularity you lose for the optimization increase you gain.

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.