I'm designing a website for an annual event. The events are similar but mostly independent year-to-year. I see 2 possible designs:
A single schema with most tables having an EventID column to tag data to specific year. Possibly have views for each year.
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?