[djemmers]

Hi,

I have a site that gives an oversight of sport tournaments in 2 countries.
It stores the tournaments in a sql db wich is editable with phpmyadmin.

Now I want to expand the site to not only tournaments but also camps, lessons,... in the sports.

While thinking about that I realised that the desing of the db that should store all the those sport events isn't that straightforward anymore and that I could use some help with it.

here is what it should have:

3 categories: camps, tournaments, lessons.
sports: volleyball, soccer, basketball,...

as I see it there ar 2 options but I wonder what is best?

db1: all the events in one table,
but with options I will add later on (language, country,...) I am affraid that it will become a verry large table and as a result slow to search/edit and so on?

db2: a seperate table for eact category:
looks better, but I still wonder if this is the way to go.

Any help or suggestion is welcome.
keep in mind that I expect those tables to become large..( hundreds per sport per category)

[mike_bike_kite]

One table and make sure you just have fields to describe

• type of sport i.e. basketball, soccer …
• type of event i.e. tournament, camp, lesson …
• country:

The above fields could have FK lookups to ensure their values are correct.

Then just make sure your indexing is correct. Most of us have tables with many millions of records and performance is rarely a problem unless the indexes are poorly defined. Speed to run won’t be much different between your “one table” and “many table” approaches but the speed to develop the application and maintain it in future will be much better with your “one table” approach.

Mike

[djemmers]

tnx for your clear reply.
but what do you mean by the following ?

Originally Posted by mike_bike_kite

The above fields could have FK lookups to ensure their values are correct....
Then just make sure your indexing is correct.

[mike_bike_kite]

You just want to have some validation on these fields to ensure that users put the right values in. This might ensure that you just have soccer in your database rather than some users entering soccer, some football, and others entering footy or foot ball etc. You can do this via foreign keys (FK) where you just put all the allowed values into a lookup table and the foreign key ensure whatever value goes into your main table is one of these values.

Obviously if you’re producing a web front end to your system then you can just populate a select option on your form so the user only has these values to select.

Mike

[djemmers]

Hi, I moved into my new house so didn't have internet for a while, hence the delay...
I am producing a web front for it, here a new pic.
Does this look ok to you guys ?
2 things I am struggling with:

* Language: now everything is only in dutch (belgium and the netherlands) but If all goes well I would like to expand to germany, france,...
so i will need the table languages. Now I have put language_idlanguage in every table. I think that would be ok...

* eventsort: every event has depending on the category and sport different possibilities for eventsort 1 to 5...
Do I store the ideventsort in table event (under eventsort1 to 5) or do I store the value of the eventsort (name from eventsort)

hope my questions are clear...