Single database schema or multiple schemas?

Question

I'm designing a website for an annual event. The events are similar but mostly independent year-to-year. I see 2 possible designs:

1. A single schema with most tables having an EventID column to tag data to specific year. Possibly have views for each year.

2. Multiple schema one per event. The events are independent, so it would be plausible to simply copy a schema and purge old data for each year.

I'd have multiple schemas for faster performance, ease of change, and programming simplicity, but I also feel that a single schema has its advantages such as database simplicity, uniformity, and easier access to old data (not common).

• Which approach is preferable?
• Any real world experiences on this?

Thanks.

Answer 1

the 'cleaner' solution would definitely using a single schema, and relate your data to a specific event, either with a EventID column, or yet better, using an Events table, relating your data to a row in this table (but keep it simple, you don't need to relate every row in every table to your event, just the 'top-most' objects)

but on the other hand, maybe 'You Aint Gonna Need It', a lot can change in a year, and you'll probably have to make a lot of changes and improvements for next years event. anyway you'll find yourself carrying over and migrating data which nobody needs or wants, so you may end up purging your database after all.

i learned to stick to the YAGNI rule the hard way several times, and keeping things simple always proofed to be the better solution. as long as you produce clean and maintainable code, it's often much simpler to add functionality, than to trim unneeded (and often untested) fat