Structure advice

[TimUSA]

I am a rookie at this so maybe some pros can help.

 

I am developing a small application for scoring yacht races for my virtual yacht club/gaming site. Now that I have the front end set for user input and ready to go, I want to setup the database so that it is organized and easiest to retrieve info.

 

The columns that would be needed are as follows, although I dont know how to best organize them. Whether it be all in on table, or in separate tables:

 

raceID: number (random generated number in the input form)

raceDate: date

seriesName: text

hostName: text

factor: number (weighting factor for scoring)

boats: number (number of boats in race)

skipperName: text (race participants)

racePoints: number (points scored this race by skipper)

matchPoints: number (match race points scored by skipper (dependent on factor))

fleetPoints: number (match race points scored by skipper (dependent on factor))

imgURL: ???? (this would contain the URL/URLs of the screen shots submitted with the race report by user)

 

I need to be able to out put the information in the following ways. I am not so worried about the how, but I am sure if I mess up the table structure this will be difficult!!:

1. Individual races within a series.

2. All the races within a series with points totaled by skipper.

3. matchPoints and fleetPoints by skipper

4. most active host.

 

 

Thanks in advance for any suggestions

Tim

 

 

 

 

 

[revraz]

Start putting things like they were in a file cabinet.

 

You'll have a race table with raceid, racedate

You'll have a skipper table with skipperid, skippername, racepoints, matchpoints (seriespoints?), fleetpoints

You'll have a boats table with boatid, skipperid

You'll have a series table with a seriesid, seriesname, raceid, boatid

Not sure what the hostname means

 

 

But something along those lines.  You'll want to relate from table to table using the id fields, but the idea is to keep the info in its own table, and to just add the id to another so they relate.

[TimUSA]

so something like this most likely. what i dont understand is the whole relationship thing, is the tie that binds...the primary key ID??

 

[attachment deleted by admin]

[revraz]

The primary key is usually best because it's unique and identifies the relation to that particular table and record.

[TimUSA]

so would you mind checking my work here:

 

 

[attachment deleted by admin]

[Anthop]

I'm not exactly sure how these boat races work, but if I'm understanding this correctly, it might actually do you more good to have a "Results" or "Points" table.  In it, you would store the number of points that a skipper got on a particular race.

 

It would look something like this:

|------------|
| Results    |
|------------|
| ResultsID  |
| SkipperID  |
| RacePoints |
|------------|

 

One skipper would probably have several entries in the Results table.  You could then total up the points to give a cumulative score.  This would require a slightly fancier front-end, but would give you the added benefit of seeing individual results as well as a cumulative score.

 

Also, this really doesn't make that much of a difference, I guess, but I would really use ints instead of varchars to store my primary and foreign keys....

[TimUSA]

thanks for all the help here. this is what we have come up with that should work the best...is always good to get the input.

 

[attachment deleted by admin]

[Barand]

What happened to boats and skippers?