Database structure - most common queries span 3-4 tables. Should I reduce tables?

I am creating a new DB in MySQL for an application and wondered if anyone could provide some advice on the following set up. I'll try and simplify things as best as I can.

This DB is designed to store alerts which are related to specific items created by a user. In turn there is the need to store notes related to the items and/or alerts. At first I considered the following structure...

USERS table - to store basic app user info (e.g. user_id. name, email) - this is the only bit I'm fairly certain does not need to be changed

ITEMS table: contains info on particular item (4 fields or so). Contains user_id to indicate which user created/owns this item

ALERTS table: contains info on the alert, item_id to indicate which item the alert is related to, contains user_id to indicate which user created alert

NOTES table: contains note info, user_id of note owner, item_id if associated with an item, alert_id if associated with alert

Relationships:

An item does not always have an an alert associated with it An item or alert does not always have a note associated with it

An alert is always associated with an item. More than one alert can be associated with the same item.

A note is always associated with an item or alert. More than one note can be associated with the same item or alert.

Once first created item info is unlikely to be updated by a user.

For arguments sake let's say that each user will create an average of 10 items, each item will have an average of 2 alerts associated with it. There will be an average of 2 notes per item/alert.

Very common queries that will be run:

1) Return all items created by a particular user with any associated alerts and notes. Given a user_id this query would span 3 tables

2) Checking each day for alerts that need to be sent to a user's email address. WHERE alert date==today, return user's email address, item name and any associated notes. This would require a query spanning 4 tables which is why I'm wondering if I need to take a different approach...

Option 1) one table to cover items, alerts and notes. user_id owner for each row. Every time you add a note to an item or alert you are repeating the alert and/or item info. Seems a bit wasteful but item an开发者_StackOverflow社区d alert info won't be large.

Option 2) I don't foresee the need to query notes (famous last words?) so how about serializing note data so multiple notes are stored in one row in either the item or alert table (or just a combined alert/item table)

Option 3) Anything else you can think of? I'm asking this question as each option I've considered doesn't feel quite right.

I appreciate this is currently a small project and so performance shouldn't be of great concern and I should just go with the 4 tables. It's more that my common queries will end up being relatively complex that makes me think I need to re-evaluate the structure.

I would say that the common wisdom is to normalize to start and denormalize only when performance data suggest that it's necessary.

Make sure that your tables are indexed properly, with foreign key relationships for JOINs.

If you think you'll end up with a lot of data, this might be a good time to think about a partitioning strategy. Partitioning your fast-growing tables by time would be a good first step.

Four tables is not complex. I commonly write report queries that hit 15 or more tables in a database structure that has hundreds of tables (most with millions of records) and I wouldn't even say our dbs are anything more than medium sized (a typical db in our system might have around 200 gigs of data, so not large at all as databases go). Because they are properly indexed, they still run fast unless I am doing very complex calculations. Normalize, don't even consider denormalizing until you are an experienced database designer who knows better than to worry about the number of tables.