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.

Anything wrong with my thinking ?
( be gentle )

your thinking is excellent

there are other threads going on at the moment regarding whether to use an id or not, but you don't really need to read them

in your case, use the Season name itself, and not a numeric id

That's a decent analysis of the issue. We are discussing these points now here:
"id" attribute in db tables...

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?

pootle flump ur codings are working excelent.

thanks,
was just reading that thread and it does answer my question nicely ( should have done that first eh )

Does come down to "space vs speed" ,
actually in my case the top priority would be my own "human user friendlyness".

As far as diskspace goes i expect maybe something like 10-15 Mb for the core database.

So if i need to use a few Mb's more but as a trade off i get queries consisting of 10 lines in stead of 20 lines i'll gladly make the trade. (within reason)