I've got a database containing information on Tennis. One aspect is Season which is a name. (it appears to be the same as a calendar year but it is not)
I have determined that for example a Tournament is only unique in combination with a Season. Same with matches which are unique inside a Season + Tournament + Round. So Season appears everywhere and is used in 90% of all queries.
Now i have a choice:
- Put Season in a table of its own with an ID and reference that ID.
- Simply put Season as a char or varchar in each table where applicable.
If i use a seperate table for Season names then i use the least diskspace but i will get a JOIN statement with this table in just about 90% of my queries.
If i duplicate the name inside other tables then i use more diskspace but my queries are simpler and do not need a JOIN. ( the primary keys on those tables do get to contain a char or varchar rather than a couple of int's and i'm not sure on the impact of that )
My thinking at the moment is:
as the database isn't that large i'm not too concerned about diskspace and i would prefer simpler queries. So i put Season is each table as a string and use a bit more diskspace but save the resources used by a JOIN in many queries.
Google surrogate Vs Natural keys if you really want to go to town.
Personally, I think your current design sounds perfectly acceptable. How many rows likely in this database? I would have thought you are talking KB, maybe MB, disk savings - not really worth the effort eh?