I am using PHP and MySQL. I've records for:

  • occasions with assorted "event types" which are hierarchical (occasions might have multiple groups and subcategories, but you will find a set quantity of such groups and subcategories) (timestamped)

What's the easiest method to setup the table? Should I've got a couple of posts (30 approximately) with enums for good or bad showing membership for the reason that category? or must i use MySQL SET datatype? http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

Essentially I've performance in your mind and I wish to have the ability to retrieve all the ids from the occasions for any given category. Just searching for some insight on the best method of doing this.

It may sound like you are chiefly worried about performance.

A few individuals have recommended splitting into 3 tables (category table plus either simple mix-reference table or a more elaborate method of modeling the tree hierarchy, like nested set or materialized path), the first factor I figured after i read your question.

With indexes, a completely stabilized approach like this (which adds two JOINs) will have "very goodInch read performance. One problem is the fact that an Place or UPDATE for an event now could also include a number of Place/UPDATE/Removes towards the mix-reference table, which on MyISAM means the mix-reference table is locked as well as on InnoDB means the rows are locked, therefore if your database is busy having a significant quantity of creates you are going to possess a bigger contention problems than if only the event rows were locked.

Personally, I'd check out this fully stabilized approach before optimizing. But, I'll assume guess what happens you are doing, that the presumptions are correct (groups never change) and you've got a usage pattern (plenty of creates) that requires a less-stabilized, flat structure. That's totally fine and it is a part of what NoSQL is all about.

SET versus. "plenty of posts"

So, regarding your actual question "SET versus. plenty of posts", I'm able to state that I have labored with two companies with wise engineers (whose items were CRM web programs ... one was really occasions management), plus they both used the "plenty of posts" method for this type of static set data.

My advice is always to consider all the queries you'll be doing about this table (weighted by their frequency) and just how the indexes works.

First, using the "plenty of posts" approach you will need indexes on all these posts to ensure that that you can do SELECT FROM events WHERE CategoryX = TRUE. Using the indexes, that's an excellent-fast query.

Versus with SET, you have to use bitwise AND (&lifier), LIKE, or FIND_IN_SET() to get this done query. Which means the query can't make use of an index and should perform a linear search of rows (you should use Show verify this). Slow query!

This is the primary reason SET is an awful idea -- its index is just helpful if you are choosing by exact categories of groups. SET is effective if you would be choosing groups by event, although not the other way round.

The main trouble with the less-stabilized "plenty of posts" approach (versus fully stabilized) is it does not scale. For those who have 5 groups plus they never change, fine, but when you've 500 and therefore are altering them, it is a large problem. Inside your scenario, with around 30 that never change, the main problem is the fact that there's a catalog on every column, therefore if you are doing frequent creates, individuals queries become reduced due to the amount of indexes that has to up-to-date. When you purchase this method, you might like to look into the MySQL slow query log to make certain there's not outlier slow queries due to contention at busy occasions of day.

Inside your situation, if yours is really a typical read-heavy web application, I believe choosing the "plenty of posts" approach (because the two CRM items did, for the similar reason) is most likely sane. It's certainly faster than SET for your Choose query.

TLDR Avoid using SET since the "choose occasions by category" query is going to be slow.

You could try utilizing a mix-reference (Xref) table, to produce a many-to-many relationship involving the occasions as well as their types.

create table event_category_event_xref
(
  event_id int,
  event_category_id int,

  foreign key(event_id) references event(id),
  foreign key (event_category_id) references event_category(id)
);

Event / category membership is determined by records within this table. If you possess a record with {event_id = 3, event_category_id = 52}, this means event #3 is within category #52. Similarly you could have records for {event_id = 3, event_category_id = 27}, and so forth.