none
Database design - issues arising RRS feed

  • Question

  • Hello all

    I am fairly new to database design, but have maintained an Access database for a while. I am putting together a database to manage a local produce show. I will have a form to log exhibitor information and entries, a form to log results, a report to view exhibitor results/entry summary (e.g. # classes entered, # entries, total fee, total prize money etc.) and a report or form to review trophy awards (e.g. most point for WI member in Produce category). Here is my current design.

    However, things get pretty complex when I consider how to do the following:

    - total points for all non-WI members

    - total points for all WI members

    - exhibitor results summary (oer exhibitor, #classes, #prizes, total sum won, etc)

    I have spent some time creating queries and then summing the results here and I am still finding issues. Although I expect this database design will work if I stick at it, I would like to ask if anyone can advise on a better design or point out where improvements can be made.

    Thanks for any help


    Ray


    EDIT: I have been asked to provide a bit more information about the data:

    • Edited by larsty Monday, September 2, 2019 8:48 PM Additional information
    Saturday, August 31, 2019 3:10 PM

All replies

  • I am fairly new to database design, but have maintained an Access database for a while.

    Hi Ray,

    You better start with "sound" Fieldnames: no spaces, no questionmark, no numbercharacter to start with, no other non-standard characters. Lateron they will give you problems.

    Instead use something like "Phone_number" or "PhoneNumber".

    The nice thing of "Phone_number" is, that for display purposes for the user, you can easily replace all underscores by spaces.

    Imb.

    Saturday, August 31, 2019 3:59 PM
  • Thanks for the hint - I thought I had normalised the database design up to 3NF. Can you see where it's not compliant?

    Thanks

     

    ------

    Saturday, August 31, 2019 5:52 PM
  • Thanks, that's a great catch. I will edit the database to do this, and then recompile and compact it.


    ------

    Saturday, August 31, 2019 5:53 PM
  • I don't know exactly what you're doing, but here is a wild guess as to the workflow: exhibitor ID can go into the exhibitor table, category table should have a secondary key named classID and linked to the classes and your results table should really be the entries. The tables are all very convoluted and irrational. Give me the workflows on what data you are gathering and how it's supposed to behave and I may be able to help a bit more.
    Saturday, August 31, 2019 6:42 PM
  • or point out where improvements can be made.

    Hi Ray,

    Ignoring the Results_1 table, I would expect the Result are related to the Entries in a 1:1 relation. Entries then is - as you designed - related to Exhibitor and Classes.

    You can omit Results, and extend the Entries table with the fields Notes and Score (Place: 1, 2, ...).

    In a formal design there is a 1:n relation between Classes and Prizes. Except for the PK field Prizes_id and the FK Classes_id you have the fields: Score or Place, Amount and Points. But if you always have only 2 Places (first and second) I can imagine to be a little less formal.

    But it is all dependent of how the data flows.

    Imb.

    Saturday, August 31, 2019 7:06 PM
  • Without sample data and a "glimpse" of what you are after is hard to judge but when your join lines "form" a closed loop is usually a recipe for something going wrong..lets say that query design must have a "flow" ..the data just flowing from one table to the next..to the next...pretty much like rivers...and rivers don't run in circles..
    Saturday, August 31, 2019 7:08 PM
  • Initial thoughts:

    Looking first at the model for classes there is a one-to-many relationship type between Categories and Classes and a many-to-many relationship type between Classes and Places which is modelled by a table which resolves the relationship type into two one-to-many relationship types:

        Categories---<Classes----<ClassPlaces>---Places

    Entries represents a one-to-many-to-many relationship type between Exhibitors and ClassPlaces:

        Exhibitors----<Entries---<ClassPlaces

    The Entries table will consequently have a composite foreign key of ClassID and PlaceID referencing the composite primary key of ClassPlaces

    The ClassPlaces table is therefore modelling a ternary relationship type between Entries, Places and Classes.  The Places table should include an 'Unplaced' row or similar.  By making whatever is the PlaceID primary key value of the 'Unplaced' row the DefaultValue property of the PlaceID column in Entries, each exhibitor entering for that class will thus be recorded as 'Unplaced' until the results are decided and the relevant rows updated to reference the rows for first and second class.

    Values like first and second prize money will be stored in a single column in ClassPlaces, of which they are an attribute, rather than being encoded as column headings as at present, in contravention of the Information Principle (Codd's Rule #1), which requires that all data be stored as values at column positions in rows in tables, and in no other way.  The same is true of points.  In both cases the value will be zero for the 'Unplaced' rows of course.

    It should be stressed that this putative model is only a first draft, and may well require some amendment when tested.  However, I think the logical model is basically sound, and would make the sort of queries required to provide the sort of answers you've cited relatively straightforward.

    As there can presumably be only one first and only one second place per class, this places a constraint on the Entries table.  This cannot be enforced by indexing, but can be by applying a suitable CHECK CONSTRAINT to the table.  It can also be applied with appropriate validation code in the relative form's module, though ideally that should not be the sole means of doing so.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Saturday, August 31, 2019 11:33 PM Typo corrected.
    Saturday, August 31, 2019 11:28 PM
  • In my opinion your design is far too complex and confusing. First, some naming suggestions:

    1. You may not use the field name "Name" as you have done in several of the tables. It is a "reserved" word in ACCESS
    2. You cannot use ? in a field name as you have done in the Exhibitor table.  The ? has special meaning to ACCESS
    3. Do not use spaces between words. ACCESS will accept them but it is just not a good idea.

    Relational databases are generally based on a hierarchy concept. In this case, you start with the Event and work from there. But if you don't want to use the Event table, then put the Entry fee field in the Exhibitor table and delete the Event table. You will also notice the tables use Primary Keys fields and Foreign Key fields. Each Primary Key field is an AutoNumber data type with  Duplicates Not Allowed. The related Foreign Keys are Number data types with Duplicates Allowed. All of these Enforce Referential Integrity and Cascade Updates and Deletes.

    Of course, you would want to include a ProduceName field in the Entries Table and any other fields you need in any of the tables.

    Below is how I would design the database. You start with the event itself

    • which can have several Exhibitors
    • which can have several  Entries
    • which can produce results

    I hope this can get you started in the right direction. Good database design is all about planning. Good luck.


    Sunday, September 1, 2019 1:33 AM

  •     Categories---<Classes----<ClassPlaces>---Places

    Entries represents a one-to-many-to-many relationship type between Exhibitors and ClassPlaces:

        Exhibitors----<Entries---<ClassPlaces

    The Entries table will consequently have a composite foreign key of ClassID and PlaceID referencing the composite primary key of ClassPlaces

    The ClassPlaces table is therefore modelling a ternary relationship type between Entries, Places and Classes.  The Places table should include an 'Unplaced' row or similar.  By making whatever is the PlaceID primary key value of the 'Unplaced' row the DefaultValue property of the PlaceID column in Entries, each exhibitor entering for that class will thus be recorded as 'Unplaced' until the results are decided and the relevant rows updated to reference the rows for first and second class.

    Hi Ken, thanks for this, very useful. I am struggling to understand the ternary table. I get that it needs to link various tables together, but I am confused about the foreign keys. Here is my new database design:

    Is this along the lines that you describe? I have added more information to my original post which describes the functionality more precisely.

    Thanks


    Ray


    ------


    • Edited by larsty Monday, September 2, 2019 8:55 PM added final sentence.
    Monday, September 2, 2019 8:52 PM
  • Thanks all for your replies. I have started another design which I was hoping to attach here as an example, but then I realised that you cannot attach files. I have copied the relationship diagram to my reply under Ken's post.

    I have also added more information to my original post.

    I will be spending more time on this tomorrow. Thanks again.


    Ray


    ------


    • Edited by larsty Monday, September 2, 2019 8:56 PM Added note about updated original post
    Monday, September 2, 2019 8:54 PM
  • I am struggling to understand the ternary table. I get that it needs to link various tables together, but I am confused about the foreign keys.

    Your model is correct.  To model the ternary (3-way) relationship type the Class_places table resolves the relationship type into three one-to-many relationship types by means of the three foreign key columns each of which references the primary key of the relevant referenced table.  This is how many-to-many relationship types should always be modelled.  Do not be tempted to use a 'multi-valued field' which is a way of modelling a simple binary relationship type, but is more trouble than it's worth.

    You'll need to include an Entry_fee column somewhere.  If there is a separate fee per class then it would be in the Classes table, but if the database is going to be used over time you would also need an Entry-fee column in the Class_places table, whose value would be assigned form that in the Classes table when a row is inserted into Class_places.  This is because fees can change over time, but you would want to retain the actual value at the time of the entry in Class_places regardless of any changes to the value of that in Classes.

    One thing you don't have in the model is a table for the overall event of which the classes are components.  At present the database would only be usable for a single event.  By adding an Event table and relating this to Entries on Event_ID you would be able to use the database for future events also.

    One amendment I would strongly recommend is that you change the names of the primary keys from ID to Category_ID, Class_ID, etc.  I know the generic name ID is used in some Microsoft templates and demonstration files, but it is nevertheless a bad idea.  Believe it or not, there is actually an ISO standard on this.  I can give you a link to it if you wish, but it's pretty turgid and to my mind goes over the top by a country mile.

    PS: The Class_place_ID column in Places serves no purpose.  The relationship type with Class_places is already achieved by the keys.



    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Monday, September 2, 2019 9:40 PM Postscript added.
    Monday, September 2, 2019 9:34 PM
  • Thanks Ken, I'm up to version 5 now! 

    I am still confused over this statement in your earlier post, specifically the parts in bold:

    Entries represents a one-to-many-to-many relationship type between Exhibitors and ClassPlaces: 
        Exhibitors----<Entries---<ClassPlaces
    The Entries table will consequently have a composite foreign key of ClassID and PlaceID referencing the composite primary key of ClassPlaces
    The ClassPlaces table is therefore modelling a ternary relationship type between Entries, Places and Classes.  The Places table should include an 'Unplaced' row or similar.  By making whatever is the PlaceID primary key value of the 'Unplaced' row the DefaultValue property of the PlaceID column in Entries, each exhibitor entering for that class will thus be recorded as 'Unplaced' until the results are decided and the relevant rows updated to reference the rows for first and second class.

    My current model looks like this:

    In the entries table, I do not see why there should be a Class_place_ID column, or a Places_ID column. I think that the default entry for Place_ID in Class_Places should be the Primary key value from the Places Unplaced row, which I think you meant and which is what I have implemented (simply defaults to 1, row 1=Unplaced)

    Next, to test this model I've created a small amount of data in the Categories, Classes and Exhibitors tables. At my entry desk I want to complete all the exhibitor information and then add the classes that the exhibitor is entering, so I created a form with the Exhibitor table fields and the Class_ID field from the Class_places table as a sub-form. However, this doesn't work and I think it is because the Class_places table does not generate a row when I try and add a class. I think I must be doing something else wrong as I expected the empty fields (Prize_money, points etc) to populate with their default values.

    Any help much appreciated, 

    Thanks again


    Ray


    ------

    Tuesday, September 3, 2019 8:31 PM
  • As your modus operandi is entry oriented, to my mind the appropriate interface would be an Entries parent form within which would be a Class_places subform, linked to the parent form on Entry_ID.  The Entries table should not contain a Class_place_ID column.  The parent form would thus contain a combo box bound to the Exhibitor_ID column.  To add a new exhibitor the NotInList event procedure of the combo box can be used, so that when a new name is entered a dialogue form will be opened in which the new exhibitor data can be entered, and the name added to the combo box's list.  I'll say more about this below.

    The subform would contain combo boxes bound to the Class_ID and Place_ID columns, again using the NotInList event procedure to add a new class or place if necessary, and a check box bound to the Entry_fee_paid column.  To avoid duplication of data and consequent update anomalies I would decompose this table, putting the Prize_money and Points columns in a separate table related to Class_Places on Class_ID and Place_ID, i.e. the primary key of the new table would be a composite of these two columns.  Entry_fee would be another column in this table, though an Entry_fee_paid column of Boolean (Yes/No) data type would be appropriate in Class_Places.

    In relational database terms what you are doing when you insert a row into Class_places is to create a relationship value of type Class_places.  All tables represent types, all of which are entity types, and some of which are also relationship types.  A relationship type is really just a special kind of entity type in the database relational model.

    I mentioned the use of the NotInList event procedure above.  This is triggered when a value is entered into a combo box which is not already represented in the list, and allows the new value to be added.  This can be done in one of two ways: (a) where only the value entered has to be entered into the relevant referenced table it can be done transparently.  An example would be adding a new category in a combo box bound to the Category_ID column in a Classes form; or (b) where data has to be entered into other columns in the referenced table in addition to the value typed into the combo box, in which case the code opens a dialogue form to add the new row into the referenced table, passing the value typed into the combo box to the dialogue form as its OpenArgs property.

    You'll find examples of the use of the NotInList event procedure in both contexts in NotInList.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file you'll see that the opening form does allow a new contact name to be entered in this way, but also points out that this is not a good way of doing so as personal names can legitimately be duplicated.  Consequently an alternative means of adding a new contact, and adding the new contact to the combo box's list by means of a command button is also illustrated.

    So, by using the NotInList event, data can be inserted into other tables without leaving the entries form/subform.  This is most likely to be necessary in the case of new entrants, as I'd imagine that the Classes, Categories and Places tables would be already filled in advance.

    One final point: I notice that you still have columns for prize money and points in the Classes table.  These are unnecessary as you would have columns for these in the new table arising from the decomposition of the Class_Places table as I described above.  Moreover, they are invalid columns as they encode data as column headings in breach of the Information Principle.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, September 3, 2019 10:49 PM Tyo corrected.
    Tuesday, September 3, 2019 10:46 PM