Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    JoeBruce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    20

    Keys (Primary, Composite, Foreign), Relationships and Database Normalization

    Hey all -

    tl;dr? Check the bold type.

    In the beginning of November I made a post about queries, and the discussion led back to the design of my database. I've done some thinking and tinkering, and a little more research. I still have a lot of uncertainty about the work I've already done, and would like more guidance. I also have a thread from September about a solved issue, which includes the file of an earlier but similar database in post #8, in case anyone wanted to see a sample of what I started.

    Firstly, aytee111 wrote in a post that "People use autonumbers as primary keys so as to disguise the underlying bad table design!" Are autonumbers generally frowned upon for use as PKs? I feel there are some instances where you would have to use one, such as my "TeacherInfo" table (see below). I've also learned about composite keys, and am curious if that would be useful to my design.

    I have brought myself back to the planning stage, and my uncertainty comes from the fact that I feel my current work has everything I need, but it's organization/normalization is slightly faulty. When I write down what I want from my database, I wind up with a plan that looks similar to what I already have. Here is the context of the database, followed by a brain dump: I run an education program, and I offer several environmental education programs to local teachers. The database will be used by me (and maybe a coworker or two) to enter, store and report teacher information. It will also enter, store and report any programs in which the teacher has participated. The data will be entered via a form which will also have an option to generate numerous reports on the data.

    I want my database to show:
    1. Information on teachers.
    2. Which programs teachers have attended.
    3. When teachers participated in programs.
    4. How many students participated in programs.

    With the data, I should also be able to run queries/generate reports that focus in on specific types of programs (e.g. how many field trips vs. in-school programs) and the attendance of those programs (how many students attended field trips vs. in-school programs).

    My design so far has included three tables (some names have changed as I've worked on this project):
    1. tblTeacherInfo (all pedigree info like name, school, grade level, contact info)
    2. tblTypesofPrograms (lists the programs that we offer)
    3. tblProgramsGiven (stores which program, which teacher(s), what date, how many students)


    Based on all this, how would I create the relationships between these tables? Do I need more tables? Should I be using any autonumbers for PKs, or can a text field be a primary key (all program names are unique, so should that be the primary key)? Should I make a composite key from 3 rows, or is that indicative of bad database design? Should a foreign key be a part of a composite key? If I need to supply more info I gladly can; I left it somewhat vague because I would like your thinking/advice to be as fresh as possible (i.e. not tainted by my lack of database design experience).



    Thanks as always,
    Joe

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    No, autonumbers are not frowned upon at all. My original point was that sometimes they are thrown in in order to have a unique key on the table, when in actual fact the unique key already exists in one or more fields.

    That being said, your first two tables are what I call "master" tables and in these cases the autonumber is the way to go - they will be the ID of the teacher and the program and will never have any meaning attached, plus there is no other unique value to use.

    The programs given has a unique key of program, teacher, date - but I prefer not to use more than 2 fields in my PK (it can get rather cumbersome), so I would use an autonumber here as well. Then programmatically ensure that duplicate records are not added to the table.

    The table containing programs could be broken out - watch out for duplication on the tables such as category or type of program (incorrect name of table, btw). You may want to create tables for these and link them to the programs master table.

    Names are definitely not the right way to go for PK's - they are free-form text fields and users are know to change their minds!

    You also may want more information on the teachers, such as what school (another table). Then you may want to think about tracking your invitations: who you contacted and when, did they reply and when, how it was sent, etc.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I run an education program, and I offer several environmental education programs to local teachers. The database will be used by me (and maybe a coworker or two) to enter, store and report teacher information. It will also enter, store and report any programs in which the teacher has participated. The data will be entered via a form which will also have an option to generate numerous reports on the data.

    It seems there is a general lack of clarity in the "specification". You offer environmental programs to local teachers.

    How does a teacher participate? What does a teacher do to participate?
    You want reports --but haven't mentioned what the report might contain? This is where a mock up of the report may be useful for communication.

    Note: I'm just going by the bolded text you offered after saying you were back at the planning stage.

    What does attended mean in relation to participated?

    As for relationships
    they are based on your business rules. That is your business and processes determine what things are related to other things.
    For example, a professor may teach many courses.
    A Student may participate in 0,1, or many activities.
    A Customer makes 1 or many Orders.
    An Order contains 1 or many Line items.

    Good luck, and I recommend you write a clear description of the business --a typical day in our business -- and add details incrementally until you have it complete. This is what a developer would want if you were to ask someone to develop such a database.

    Spend 30-45 minutes working through this tutorial and you'll get a better appreciation for relationships and the database design process. You end up with a database design (blueprint) that matches your specification.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    RE: Autonumbers
    Should I be using any autonumbers for PKs, or can a text field be a primary key (all program names are unique, so should that be the primary key)? Should I make a composite key from 3 rows, or is that indicative of bad database design? Should a foreign key be a part of a composite key?
    I use Autonumbers for 99.9% of my tables. If the table is a look up table, I generally won't have a PK field, but all other tables do have an Autonumber as the PK field.
    I NEVER use a text field as a PK field, nor use composite (multi-field) primary keys.
    If I need to ensure no duplicate records, I use a multi-field Index.

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp


    I played around with your dB "Sample Database - with duplication issue" and it seems to working OK. Still need to create the reports, but.......
    Attached Files Attached Files

  5. #5
    JoeBruce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    20
    Perhaps I've been thinking about this backwards. I at first focused on building a database to store the teacher info, and show which programs they've attended. Now I'm thinking the focus should be on storing the programs I've given, and show which teachers attended. It will have all the same information, but the structure is different. If I do this, then my "ProgramsGiven" table would have to be able to store multiple teachers for a single program, which is where I seem to be hitting my current roadblock. I believe the teacher field would qualify as a foreign key, but I haven't worked out the full relationship yet... see "edit" below

    From aytee:
    The table containing programs could be broken out - watch out for duplication on the tables such as category or type of program (incorrect name of table, btw). You may want to create tables for these and link them to the programs master table.
    Some of the "training" I've been doing coincides with this advice. I have 4 types of programs (e.g. Field Trip is one type, Classroom another type...), and there are several of each type (e.g. a 2-hour field trip and a 4-hour field trip) - so I should actually have a separate table for each type of program? (Also, is this why you say the table name is incorrect?) How exactly do these "link to the programs master table"? I feel foolish asking what seems simple, but I've come this far down the rabbit hole. On the form I want a drop-down box which allows me to select from the various programs so I don't have to type in the name of a program - that's how I have it right now, but unsure how to do this when things are split out into multiple tables.

    Thanks to all who have replied. I'm working the tutorial orange included which seems to be helpful so far, especially once I started to rethink the focus of the database (programs over teachers, not the other way around).

    edit: After working through that tutorial, I realized my design had a many-to-many relationship. I have not been using Access, just Word and Publisher to plan things. I went with a linking table (TeacherAttendance) and 2 one-to-many relationships... one teacher can attend multiple (or zero or one) programs, and one program can have one or more teachers attending. All I would have in that "TeacherAttendance" table is a composite key made of two fields. See attached Publisher file for a visual. I'm still struggling with how to organize and create the relationship between the ProgramListing and Programs tables, though.
    Attached Files Attached Files

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I can't see the difference in your design as per your change in focus. Your ProgramsGiven table was always able to handle multiple teachers per program (as well as multiple programs per teacher), it is the "hook" between the two main tables, a transaction table, storing teachers that attended a program (etc.).

    For creating other tables as I mentioned above (one table per thing, not multiple) - e.g. a ProgramTypes table would contain a PK and a description. The PK would be stored on the ProgramsGiven table. IMO these kinds of tables are necessary parts of database design. On your form your combobox would display the values from this table to select from.

    It would be up to you to decide whether "field trip" is a type or "2-hour field trip" - in the former case the duration could either be on another table or maybe on the Programs table, depending on what you call the program there (2-hour, etc). Somewhere you need to carry the duration, better to keep it separate from the programs table so that you can give proper names/descriptions to your programs. As I am writing this it seems that "2-hour field trip" is the way to go, on a Types table, I am sure your database isn't going to become that large or complex.

    To summarize, when entering a program given, you would have comboboxes for program, teacher, type and would store the PK's from these tables on the ProgramsGiven table.
    Last edited by aytee111; 12-29-2016 at 05:03 PM. Reason: didn't make sense

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have to agree with aytee111.... not much difference in the two dBs structure.


    I played around with your dB a little more....
    Attached Files Attached Files

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As I was reading this, I thought "I bet there will be a comment about never using composite PK fields". Unless someone can point me to a good reason why not (and other forums where agreement with this opinion is all that is offered does not count) I remain convinced it is just another way to skin a cat. Sure you can create composite indexes, but composite PK's are easier to create. That is not the reason why I have and would continue to use composite pk's. I have probably said this elsewhere in this forum, but where I worked, there was an enormously complex and huge database which was created in house (there were more people in the IT and IS world there than some companies have on their entire payroll) which by the time I left, had a $10,000,000 price tag on its development. Yes, that is 10 million dollars - and it had many tables with composite PK's. I figure if it was good enough for them... And no, the overwhelming portion of that money was not fixing bugs but from feature development. It did RCA's FMEA's, purchasing, stores, picklists, work requests, work order management, purchase orders, safety, work instructions, BOL's, condition monitoring, real time vibration monitoring and probably stuff I've forgotten at the moment.

    I don't see the point in an autonumber field if it's never going to be used or presented, but I often include them on the off chance that I might have to refer to it in some list or combo box as a pointer to a unique record. So far, I cannot remember ever needing to do this, and I would not bother with them if the table is a lookup table - a unique/no dupes index would be my approach. If you have a Dept|WoNo (department, work order number) PK, that means you can have MACH 123 and WELD 123, and both are unique. A composite index will achieve the same thing, but to what advantage? Don't get me wrong, I'm not saying there is anything wrong with electing to never us composite PK's; I just hope that one day someone will be able to justify why not by some other reason than preference. I for one try to include in my posts that some say no, but I say why not, and leave it to the OP to decide.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good dialog Micron. My guess is that the $10,000,000 system was not Access based. Many people and books will show the use of multiple contributing primary keys in junction tables. This in effect is a composite PK. And is often the proposed approach rather than a separate autonumber or "sequence".
    If Junction/lookup tables have additional relationships, the PK structures can get "unwieldly"/"quite awkward". The decision then is a separate PK and a composite unique index of the related PKs.

    Just my 2 cents.
    Last edited by orange; 12-30-2016 at 03:43 PM.

  10. #10
    JoeBruce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    20
    @Steve -

    Thanks for working on this (I didn't even realize your first post had an edited version of my sample!) I guess my "structure" comment was more in my head than something tangible to point to in the DB...

    When I first looked at your "MOD2," I thought you managed to fix my query problem, which is what prompted this whole ordeal. Looking again, I realize that it is still present. The problem involves running a query to see how many students have come on programs; I have some programs with multiple teachers, but the number of students is the same (the total number of students gets recorded for all teachers, not specific class size for each teacher). The query results include both teachers that came on a single program, so it counts the students twice (bloating my stats). This led me back to the basic design of my DB.

    Unfortunately, when I make changes in a more current database and design this query, it returns no results whatsoever.

    There is a difference between your two MOD files I am curious about. The "ProgramType_FK" field in "tblProgramsAttended" - the field displays a text string in the first MOD, but just a number in the second. I understand the number corresponds with the PK from "tblProgramInfo" but I would like to know what you did to change this between the two versions you posted (again, I have my suspicions). Looking back through my own numerous "backup" versions of this database, some of mine are text of the program name (like the first MOD), others are a number (like the second).

    With a little more time I think I will have the product I'm striving for. It seems I was correct in thinking I had all the right ideas and information, and the design just needed a few tweaks. Again, I really appreciate the advice and work done, but I am still trying to learn the how of it all
    Last edited by JoeBruce; 12-30-2016 at 02:23 PM. Reason: thought query problem was fixed, but it was not

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My guess is that the $10,000,000 system was not Access based.
    That would be correct, written in one or more versions of C (PowerBuilder) with, I believe, Oracle in the beginning on a dedicated server. It might have migrated to a different sql server at some point, but all my apps were written in Access with ODBC links to those tables. As for lookups, those sorts of things were really just separate tables of entities such as Department. All it takes to get a listing of work orders based on department is to filter out by dept in the query. The composite key only comes into play when entering data into the wo table. It's also the way I learned (adopted) when that sort of schema is required.

    @JoeBruce; sorry for hijacking your thread a little bit.
    Last edited by Micron; 12-30-2016 at 03:18 PM. Reason: clarification

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Micron, for the record, I didn't say composite (multi-field) PK fields were bad, just that I never use them.... for 2 reasons:
    1) I read (a long time ago) the article at FMS (2nd link in Post #4) and most fields in composite PK fields seem to be text fields.
    2) I am lazy ... uhhh... I conserve energy! For me, it is easier and faster to add an autonumber PK field than to try and come up with non-text composite PK fields. With an autonumber field, I know I have a unique value for the PK field.

    I know there is an ongoing discussion on the merits of natural keys, composite keys and surrogate keys. All three are accepted practices....... I have opted for the autonumber option....


    @JoeBruce, after re-reading the thread, this started sinking in:
    I have some programs with multiple teachers, but the number of students is the same (the total number of students gets recorded for all teachers, not specific class size for each teacher). The query results include both teachers that came on a single program, so it counts the students twice (bloating my stats). This led me back to the basic design of my DB.
    What I think you are saying is that when you have a program that has 20 students that attend and 3 teachers, the 20 gets added to all three teachers, resulting in a total of 60 students instead of the actual 20. Close?

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @ssanfu, with all due respect, if you read my post carefully again, you should conclude I didn't say that you said composite pk's were bad. As for the link, I find the information in that site contradictory, or at least confusing, and I quote:

    ...the key field (or fields) define uniqueness for that record.
    and
    The primary key should be the first field (or fields) in your table design. While most databases allow you to define primary keys on any field in your table, the common convention and what the next developer will expect, is the primary key field(s) coming first.
    How can you have two or more fields making up a pk in one table unless they are composite?

    Sometimes people justify composite (multi-field) primary keys to avoid duplicates. This is the wrong approach. To prevent duplicates, create a secondary index for those fields and define it as unique. That lets the secondary index handle that job while the single field primary index retains its efficiencies.
    This also makes no sense to me. A pk with a secondary index could not have, for example
    MACH | 123
    MACH | 456
    because the first field, being a pk, would not allow it. A composite pk or index is the only way. In the end, I don't think either way is more correct than the other, and the reason given at that site for not doing so isn't conclusive enough for me even if the statements were not contradictory. I guess we each have our own preferences. Anyway, I risk belaboring the issue if I don't cut it off here.

  14. #14
    JoeBruce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    20
    @Steve -

    Yes that is correct. It seems a weird way to track things, but I want to record the individual teachers and the total number of students. When entering data, I suppose I could just divide the number of students by however many teachers, but I feel there should be a way to design the database which makes that unnecessary. How would I design a query that only counts one instance of the program (the date, school and program type/name would all be the same)? That was the subject of the November thread (first link in my OP). I am going to take another crack at that right now.

    Seeing how you named the PK's and FK's was extremely helpful. The exercise I did also seemed helpful - anyone have any thoughts on the Publisher file I posted? Specifically the idea of creating a linking table between Programs and Teachers.

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I came up with something similar to your publisher diagram.

    Take a look at the dB. I didn't create a report to sum the students because the data is suspect (to me) and I'm not sure what you want.
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help allocating Primary/Foreign Keys and Relationships
    By rosscortb in forum Database Design
    Replies: 2
    Last Post: 07-08-2015, 08:12 AM
  2. primary and foreign keys?
    By themebuddies in forum Access
    Replies: 1
    Last Post: 12-14-2014, 09:23 PM
  3. Replies: 1
    Last Post: 05-24-2012, 09:35 AM
  4. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  5. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums