Database for Flowershow, with awards history

[Mr_Si]

Hi all,

Been a while since I've last been on here, but I have a new project that I hope you may all be able to help with.

I'm creating a new db for a Flower Show which happens on a yearly basis, which incorporates scoring for 1st, 2nd, 3rd, 4th, Highly Commended and Commended (where applicable).

I'm ok with Entrants and different classes and the general scoring etc.

However the thing I would like to do, would be for the database to become a cleansheet each year, but retain information about the entrant's previous awards.

There is also a "Banksian Medal" which is is the most points in the show, but cannot be won by the same person within 3 years of winning it previously (another reason why I want to be able to keep award history).

To add annoyance, each year, the entrant may receive a different entrant's number, rather than keeping it the same, as it's operated on a "first come, first number" basis.

Since this a completely voluntary databse, I don't want to change this if possible.

I was asked to do it as a spreadsheet this year, which is the first time the calculations have been computerised.
In doing this, it was quite time consuming entering in all the entrants and results and I think a database with a nice UI may be a quicker, better, more efficient way of dealing with it.

So regarding the years and result history etc, I look forward to your opinions and ideas.

Whilst writing this, I've had the idea that since I've made the Entrant ID an AutoNumber ID a completely independent field to the Entrant's Number, I might just not "display" the Entrant number in the history and display it by name only.

Feel free to ask questions if there's something that is not clear.

 

[jdraw]

You may want to review some existing data models at

http://www.databaseanswers.org/data_models/index.htm

I don't see anything specific, but you may get some ideas.
For more info on design, I recommend the first few topics at this site
http://www.rogersaccesslibrary.com/forum/topic238.html

 

[Mr. B]

There would be no need to "change" anything about how business is conducted. It is my opinion that any good database should be structured to model the business requirements, not the other way around.

I would suggest that you have a table with a primary key and will hold the names, contact info and and other information directly related to those who will potentially become an entrant. Let's call this table "tblRegisterations".

Then have another table, we'll call it "tblEntrants". This table will have it's own primary key. It will also have a foreign key (number - long integer) and other fields related to the flower show for the current year. It will have the record ID from the "tblRegisterations" table for each Entrant for the current year. It will potentially have several fields but one field would be the current year. Another table of awards would be needed and the record id from this table could then be oinked to the appropriate record(s) in the "tblEntrants" table.

It was not clear if the same individual entrant could receive more that one award in any one year but if so, you would actually have a many-to-many relationship between the "tblEntrants" table and the "tblAwards" table. If this is the case, you will need a table that will be a "linking" table between the "tblEntrants" table and the "tblAwards" table. It would only have the record ID from the "tblEntrants" table and the record ID from the "tblAwards" to link them together. It could also have its own record ID if you like.

Hope this helps.

 

[Steve R.]

To add annoyance, each year, the entrant may receive a different entrant's number, rather than keeping it the same, as it's operated on a "first come, first number" basis.

A solution would be a table that has a foreign key associated with each entrant, the year, and the entry number for that year. That way you would have a permanent (unchanging) number to identify each entrant and their entry number for a particular year.

Whilst writing this, I've had the idea that since I've made the Entrant ID an AutoNumber ID a completely independent field to the Entrant's Number, I might just not "display" the Entrant number in the history and display it by name only.

Do NOT have the entrant number as an auto-number ID field.

There is also a "Banksian Medal" which is is the most points in the show, but cannot be won by the same person within 3 years of winning it previously (another reason why I want to be able to keep award history).

You can have a logical (Boolean) field for the "Banksian Medal", then use Datediff to compute eligibility.

However the thing I would like to do, would be for the database to become a cleansheet each year, but retain information about the entrant's previous awards.

Have a field called something like "AwardYear", that will differentiate historical data from current year data.

You will also need a table of entrant contact information. This table will require a an auto-number primary key field that will link back to other tables, such as the award history and prior entry numbers.

 

[Mr_Si]

Hi all, thank you for your replies. They are very helpful.

Mr. B, your comments seem particularly favourable and probably the way I would want to work.

There would be no need to "change" anything about how business is conducted. It is my opinion that any good database should be structured to model the business requirements, not the other way around.

I have to completely agree, but thank you for your reminder to keep my on the straight and narrow.

Then have another table, we'll call it "tblEntrants". This table will have it's own primary key. It will also have a foreign key (number - long integer) and other fields related to the flower show for the current year. It will have the record ID from the "tblRegisterations" table for each Entrant for the current year. It will potentially have several fields but one field would be the current year. Another table of awards would be needed and the record id from this table could then be oinked to the appropriate record(s) in the "tblEntrants" table.

The foreign key in the entrants table - is this basically another way of describing a lookup field to the primary key of another table / qry?

It was not clear if the same individual entrant could receive more that one award in any one year but if so, you would actually have a many-to-many relationship between the "tblEntrants" table and the "tblAwards" table. If this is the case, you will need a table that will be a "linking" table between the "tblEntrants" table and the "tblAwards" table. It would only have the record ID from the "tblEntrants" table and the record ID from the "tblAwards" to link them together. It could also have its own record ID if you like.

Sorry, I should have said. Yes this is the case and something I've already done.


I tend to make queries directly related to the table, with all the fields included, but add an extra one, with a more humane "expression" linking a few of the other fields together, so for example: Entrant Number, First Name and Surname.
Then, when I insert them as lookups to another table, I lookup to the query rather than the table and hide all of the fields that are not required.


Steve R.

Thank you also for your reply,

Do NOT have the entrant number as an auto-number ID field.

Just to put your mind at rest, I was trying to explain that I was definitely not going to do this.

Looks like you are saying the same kind of thing as Mr. B regarding Person vs Entrant and the use of a couple of tables with one referring to another.

I still need to get my head around it though! (It takes me a while to get it into my head when someone explains something - probably because I don't do it a lot).