CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Imashcha
Imashcha

Reputation: 324

Should I use a single table for many categorized rows?

I want to implement some user event tracking in my website for statistics etc. I thought about creating a table called tracking_events that will contain the following fields:

| id (int, primart) |
| event_type (int) |
| user_id (int) |
| date_happened (timestamp)|

this table will contain a large amount of rows (let's assume at least every page view is a tracked event and there are 1,000 daily visitors to the site).

Is it a good practice to create this table with the event_type field to differentiate between essentially different, yet identically structured rows?

or will it be a better idea to make a separate table for each type? e.g.:

table pageview_events

| id (int, primart) |
| user_id (int) |
| date_happened (timestamp)|

table share_events

| id (int, primart) |
| user_id (int) |
| date_happened (timestamp)|

and so on for 5-10 tables.

(the main concern is performance when selecting rows WHERE event_type = ...)

Thanks.

Upvotes: 1

Views: 442

Answers (1)

spencer7593
spencer7593

Reputation: 108480

It really depends. If you need to have them separated, because you will only be querying them separately, then splitting them into two tables should be fine. That saves you from having to store an extra discriminator column.

BUT... if you need to query these sets together, as if they were a single table, it would be much easier to have them stored together, with a discriminator column.

As far as WHERE event_type=, if there are only two distinct values, with a pretty even distribution, then an index on just that column isn't going to help much. Including that column as the leading column in a multicolumn index(es) is probably the way to go, if a large number of your queries will include an equality predicate on that column.

Obviously, if these tables are going to be "large", then you'll want them indexed appropriately for your queries.

Upvotes: 3

Related Questions