DB for Competition

[HiTechCoach]

Come to think of it - the scores could basically be added to the "entries" table. Why split it? Beats me...

Why split it? Because this is a relational database not a spreadsheet.

TIP: If that is how you would do it in Excel (spreadsheet) then it is probably not the correct in a relational databass (Access).

Are you design a spreadsheet or a relational database?

The design you are describing may be done in a spreadsheet. Definitely not the way you would do it in a well designed a relational database. Because what you proposed would be a de-normalized design cause repeating fiedl and should NOT be done,


You do need a scores table. At the least each judges scores will be in a separate record. I am really thinking that probably each score should be a separate record.

The way I see it this:



Class/level (1) > has > (many) entries. each entry (1) >> has >> (many) judges. Each judge (1) >> has >> (many) scores.

Would it help if I create an example?


Boyd Trimmell aka Hitechcoach
Database Architect and Problem Solver
Microsoft MVP - Access Expert
25+ years specializing in Accounting, Inventory, and CRM systems
Started with Access 2.0.
"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do."

[HiTechCoach]

Well - yeah, the performance of the entries get scored. So for each entry there's one row in the "scoring" table.

I am trying to help you see how to design your database by asking you specific questions. The intent is that if you will answer the question directly it will hopefully help you change your thinking from spreadsheets to o relational database designs.

[LedZled]

Yeah, an example would definitely help. I feel like I'm stuck in the Excel way of thinking.

[LedZled]

Another way to look at this:
I have a method for entering scores at our cheer leading competitions. This method is through an Excel sheet. Once the sheet is ready with all teams in place and the formulas for ranking adjusted, it's very easy to understand how to fill it in.

What I'm looking for now is the same ease in filling in scores and caclulating results, but with less hassle setting it up for each competition with different teams. This is where the database comes in. I don't know aything about relational databases and I get totally lost when I use a lookup column and choose to use field text for relationships, still all I get out are numbers.

I see this with 3 forms; 1 for entering all teams into the database, 1 for entering the scores and one for entering deductions.

And then one report for ranking.

But I guess I'm still way off...

[HiTechCoach]

I will put together an example database with the tables the way I would create it..

Making the switch from Excel to Access is difficult in you have not studied and understand database normalization.

With a relational database:
1) tables store the data in a normalized format.
2) reports use queries to de-normalized the data. The recordset for a report does not normally resemble the table design
3) forms are use to enter data into the tables. A Form's design tends resemble the table structure a lot closer than reports. Forms rarely have the same format as reports.

It is a real paradigm shift switching to relational databases from spreadsheets. Compared to databases Spreadsheet are basically a report with de-normalized data that is used data entry. That is why they are not very efficient for managing data.

[HiTechCoach]

I have attached a database with just the tables.

Since the total score for a judge is a calculation based on other fields in the record it is not stored. It should be calculated as needed.

TIP: Use a sub form to show the data from related tables.

[LedZled]

Hi again,
it's been a while.
First, the competition which started the idea needed my undivided attention. Right after that I caught a nasty cold, from which I'm still not fully recovered.
Anyways, now I'm back for at least a quick reeturn, and I found your example database. Will have a look at it and see if it helps me see the logic.
But for now : thanks a lot, Coach!
//Led

[HiTechCoach]

Hi again,
it's been a while.
First, the competition which started the idea needed my undivided attention. Right after that I caught a nasty cold, from which I'm still not fully recovered.
Anyways, now I'm back for at least a quick reeturn, and I found your example database. Will have a look at it and see if it helps me see the logic.
But for now : thanks a lot, Coach!
//Led

No problem.

I will be here when you have more questions.