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?
