I am a newcomer for this so you might want to bear beside me. I am creating a database for any website with athletics ratings in it and that i was curious regarding the number of tables will be the most effective method of accomplishing this.

I presently have 2 tables, a table known as 'athletes' which supports the particulars of my runners (potentially around 600 people/records) which consists of the next fields:

mid (member id - primary key)
firstname
lastname
gender
birthday
nationality

An additional table, 'results', which holds all their performances and it has the next fields:

mid
eid (event id - primary key)
eventdate
eventcategory (road, track, area etc)
eventdescription (100m, 200m, 400m etc)
hrs
minutes
seconds
distance
points
location

The 2nd table has around 2000 records inside it already and potentially this can quadruple with time, due to the fact you will find around 30 track occasions, 10 area, 10 road, mix country, relays, multi-occasions etc and when you will find 600 sports athletes during my first table, that translates to a lot of records during my second table.

What exactly I'm wondering is will it be cleaner/more effective to possess multiple tables to split up track, area, mix country etc?

I wish to make use of the database to order peoples results according to their performance. If you'd like to know better things i am attempting to emulate, have a look only at that website http://thepowerof10.info

Thanks,

Mark

Altering the schema will not change the amount of results. Even when you split the venue right into a separate table, you will still have one result per participant each and every event.

The possibility advantage of getting another venue table could be better normalization. A jogger might have many results, along with a given venue might have many results on the given date. You will not need to repeat the venue information in each and every result record.

You will want to give consideration to indexes. Every table should have a principal key. Add more indexes for posts you utilize in WHERE clauses whenever you choose.

Here is a discussion about normalization and what it really often means for you personally.

PS - 1000's of records will not be an problem. Large databases take presctiption an order of giga- or tera-bytes.

My thought --

Don't break your occasions table into separate tables for every type (track, area, etc.). You will have a much simpler time querying the information out whether it's all there within the same table.

Otherwise, your two tables look fine -- it is a good start.