Results 1 to 13 of 13
  1. #1
    dgaletar is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Washington, DC
    Posts
    85

    Help with Database Planning

    I am in the process of designing a new database from scratch. Unlike anything that I have done in the past, I am trying to do this one properly from the start.

    In an effort to do that, I was following the steps that "orange" laid out in this thread. This is what I have come up with this far:

    Database Design Planner.pdf



    Would you guys mind taking a look at it and telling me if I am on the right track here???

    Thx!

    DG

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Where does pay sheet fit into your model? Are you now adding attributes to your proposed tables?
    What software did you use for your ERD?
    Looks like a thought out approach.

    Good luck.

  3. #3
    dgaletar is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Washington, DC
    Posts
    85
    Where does pay sheet fit into your model?
    I thought that the pay sheet would just be a report from information collected in the tables!?!

    Are you now adding attributes to your proposed tables?
    By this do you mean entering data???

    What software did you use for your ERD?
    Microsoft Word

    Looks like a thought out approach.
    Thank you... it was.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I guess what I'm asking is what are the fields in your proposed tables?
    Your Pay sheet info is something you want to report/determine at any time. But you haven't mentioned Pay sheet or the fields that might be involved any calculation of same.

  5. #5
    dgaletar is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Washington, DC
    Posts
    85
    Unless I'm reading it wrong, according to your planner,
    "...at the implementation phase, entities become tables and attributes become fields"
    Therefore the answer to your question
    what are the fields in your proposed tables?
    is answered in item #2 of my planner, the "Entity-Attribute Grid - Game tracking database". The "Entities" are the column headers in my grid, and they become the "Tables". The "Attributes" are then the items listed in those columns, and they become the "Fields".

    ...right???

    So, according to your planner, my database would look something like this:

    TABLE 1: GAME INFORMATION
    FIELDS: Number*
    Date
    Start Time

    TABLE 2: LEVELS
    FIELDS: Level*
    Sub-Level
    League/Division
    Season
    Season Part
    Fee

    TABLE 3: SEASONS
    FIELDS: Season*
    Season Part

    TABLE 4: LOCATIONS
    FIELDS: Location*
    Distance Traveled

    TABLE 5: PARTNERS INFORMATION
    FIELDS: First Name
    Last Name
    Cell
    Email*
    Lead

    TABLE 6: GAME/PARTNER
    FIELDS: ???

    TABLE 7: GAME HISTORY
    FIELDS: Number*
    Changes

    As for the "Pay Sheet", Every game completed at a certain "LEVEL" would be billed at the "FEE" assigned to that "LEVEL". So, for example, if I were to complete TWO (2) games with the following information:

    "HS" (Level)
    "Varsity" (Sub-Level)
    "WABC" (League/Division)
    "Spring" (Season)
    "Regular" (Season-Part)
    "$25.00" (Fee)

    ...I would bill them $50.00. But wouldn't that be done in a query?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    A few points for clarity.

    I pointed you to a tutorial that I think is great for identifying entities/tables; attributes/fields for those tables and to create relationships representing business rules between the tables. The tutorial is structured into some procedures/steps to do things in a consistent manner. I did not write it. If you follow the steps, you should arrive at a database structure that matches your stated requirements. If you look at the solution in the tutorial you will see the result of the procedures as they are applied to the original business description.

    Three things that are often overlooked by new database developers:

    1) documenting each of the the entities/tables/subjects in a clear, concise manner that distinguishes it from all of the other entities/tables. The description should be in terms that users understand and have no abbreviations or jargon. This description should be understandable by all and there should be no confusion concerning what this entity/table/subject represents to the business.
    Further each attribute/field should be identified and clearly described. The attribute should be associated with the entity/table. This documentation step will often show missing or hidden or similar entities. It will greatly help clarify the meaning of the table and attributes to all involved.

    2) naming each of the entities/tables and attribute/fields in a manner that is clear and unambiguous. Experienced Access developers will recommend you do not use embedded spaces nor special characters (punctuation, symbols) in your names.
    Here is a link to a tutorial on Naming.

    3)sample scenarios are used to test or verify the data model. After identifying the business rules and by creating some test conditions, you can "play stump the model" to verify/confirm the evolving data model. You populate your tables with some representative data and you identify what you expect the test conditions will show. This could be updating certain fields or having information required for a report. If any of the test conditions doesn't match your expected result, you must identify "what is wrong". Is it the data? Is it the data model? Is it the test condition? You must reconcile these and modify whatever is determined to be the cause of the analogy. Then retest until you are satisfied that the data model supports the identified business rules. Now you can proceed to build the database.
    It is a good idea to have users and others participate in "stump the model". The whole idea is to critique the model; prove that it does what is intended; and/or adjust as necessary until it does. It is not an exercise to attack the modeller.
    All of this can be done with pencil on a piece of paper.

    Back to your plan. Do you have all of the fields identified and described? Have you updated you data model from your earlier post?

    Consider that your plan is a specification for a database that you want someone else to build. I know you will build it, but you are "too familiar" with the details. So pretend the specification is for someone else. Is there enough information for them to build this database? Put some imaginary data into your tables (again this can be on paper) - can you get the query result you want?

    All of the documentation/specification will serve multiple purposes as you move forward. It overviews the application. It has details for anyone maintaining the database. It is an excellent foundation if any modifications or additional features will be added.

    Good luck. Post back as you proceed.

  7. #7
    dgaletar is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Washington, DC
    Posts
    85

    OK... It would seem that we are on our way!!!

    OK orange, I gave a lot of thought to what you said. Thank you for taking the time to put together such a thought provoking response. I’ll try to make my responses worthy of your effort. Anyway, here it goes...


    CLARIFICATION:
    I did not write it.
    Sorry for the confusion. For some reason I thought that you did.

    NOW TO THE DESIGN:
    If you follow the steps, you should arrive at a database structure that matches your stated requirements.
    This is DEFINITELY my goal!

    Documenting each of the entities/tables/subjects in a clear, concise manner that distinguishes it from all of the other entities/tables. The description should be in terms that users understand and have no abbreviations or jargon. This description should be understandable by all and there should be no confusion concerning what this entity/table/subject represents to the business. Further each attribute/field should be identified and clearly described. The attribute should be associated with the entity/table. This documentation step will often show missing or hidden or similar entities. It will greatly help clarify the meaning of the table and attributes to all involved.[/
    I think that I did this with my “Narrative”. Do you think that I did it right? And if not, what might be missing?

    Naming each of the entities/tables and attribute/fields in a manner that is clear and unambiguous. Experienced Access developers will recommend you do not use embedded spaces nor special characters (punctuation, symbols) in your names. Here is a link to a tutorial on Naming.
    Another great tutorial. Thank you. As for the “Naming” process, I hadn’t gotten to that point yet. I’m still struggling with making sure that I have the “Narrative” and the “Attribute Grid” setup properly. But, just to be safe, I went back and renamed everything using ideas derived from that tutorial.

    Sample scenarios are used to test or verify the data model. After identifying the business rules and by creating some test conditions, you can "play stump the model" to verify/confirm the evolving data model. You populate your tables with some representative data and you identify what you expect the test conditions will show. This could be updating certain fields or having information required for a report. If any of the test conditions doesn't match your expected result, you must identify "what is wrong". Is it the data? Is it the data model? Is it the test condition? You must reconcile these and modify whatever is determined to be the cause of the analogy. Then retest until you are satisfied that the data model supports the identified business rules.
    GREAT idea! I did this using a blank Excel worksheet and the results were eye opening! So I made a few changes and attached the results:

    <Attachments 1 & 2>

    Now you can proceed to build the database.
    I prefer to wait until it has been reviewed by you (or someone else willing to critique it). The last thing that I want to do is build something that I have to change over and over again. If I know that I am on the right track to this point, I will proceed.

    Back to your plan. Do you have all of the fields identified and described?
    I feel pretty confident that I do… now.

    Have you updated you data model from your earlier post?
    Yes. It can be see in the 2 attachments to this post.

    Put some imaginary data into your tables (again this can be on paper) - can you get the query result you want?
    I am not sure as I do not know how to figure the “query results” without building a query. (I guess that my mind won’t wrap around this one.)
    Attached Files Attached Files

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The Levels table should just include SeasonPK as a FK to Lookup Season Details.
    Also you could have a LeagueDivision table and just use the PK to identify the details in tables where you need L/D details.

    You have done a great job on analysis and documenting fields/attributes of Tables.

    When you see/identify tables that seem to be redundant -- it's time to ask more questions and get to the point where
    a) it is the same thing or
    b) they are different because...

    ONce you knnow whether it's a or b from a knowledge point of view, then move on.

    Good luck.

  9. #9
    dgaletar is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Washington, DC
    Posts
    85

    Feeling GOOD!!!

    OK, orange,

    You have done a great job on analysis and documenting fields/attributes of Tables.
    Thank you! I'm feeling pretty good about this one. More importantly, I feel like, for the first time, I am really learning (and understanding) "HOW" to build a database the right way!

    The Levels table should just include SeasonPK as a FK to Lookup Season Details.
    Also you could have a LeagueDivision table and just use the PK to identify the details in tables where you need L/D details.
    While I was doing this, I started to think that I could take your advice a little bit farther. In other words, if I were to do that with the "Levels" table and the "LeagueDivision" table, why couldn't I do it with the the other items in the database?

    Ultimately I ended up breaking EVERYTHING down like that (see attached REVISED PDF). I think that it looks real good. Here's what I did:

    • I removed the "SeasonsPart" column from the "Seasons" table
    • I created a new table for the "SeasonsPart"
    • I changed the name of the "Levels" table to "PayScale"
      • Now every column is a Primary Key except the last, the fee

    • I created a new table for the "Levels" which holds only the top-tier of levels
    • I created a new table for the "SubLevels"
    • I created a new table for the "LeagueDivision"


    Your thoughts???

    Table Layout-2.pdf

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Looks good. Why is there no PK on PartnerInfo table?

    Have you looked at relationships between tables yet?
    I'm surprised that you have data values in your table mockups and that you have assigned values in the tPayScale table without actually creating a entity relationship diagram (or have not published it with your current documentation).

    Good stuff

  11. #11
    dgaletar is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Washington, DC
    Posts
    85
    Why is there no PK on PartnerInfo table?
    There is. I used the email, as that was the only thing unique to each person.

    Have you looked at relationships between tables yet?
    I tried with my initial post, but it didn't feel right. This is a where I started loosing my grip of that tutorial.

    I'm surprised that you have data values in your table mockups and that you have assigned values in the tPayScale table without actually creating a entity relationship diagram (or have not published it with your current documentation).
    I simply put that data in so I could follow what I was doing. It simply helped it all to make sense as I was creating it.

    So it is obviously on to relationships! Not my strongsuit. Any suggestions???

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I wouldn't use email as a PK. A person's email could change. Keep email as a field. Use an autonumber as a surrogate key --the PK is primarily for system use, and should be of no real concern to you. It is simply to provide a unique identifer to each record in the table.
    Some links:

    http://www.databaseprimer.com/pages/...relationships/
    http://r937.com/relational.html <----quite thorough

    Here's a video -- good overview

  13. #13
    dgaletar is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Washington, DC
    Posts
    85
    Got it. I'll change that PK to an autonumber and get into those tutorials on Monday. Thanks again, and have a GREAT weekend!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with Table Planning in Student Database
    By whitelexi in forum Access
    Replies: 5
    Last Post: 03-09-2014, 03:08 PM
  2. Planning calendar
    By Abedecain in forum Reports
    Replies: 1
    Last Post: 01-10-2013, 04:07 PM
  3. Diary Planning Database?
    By Sawyer05 in forum Database Design
    Replies: 1
    Last Post: 02-16-2012, 06:27 PM
  4. Planning my first Access DB
    By swankinrosco in forum Access
    Replies: 11
    Last Post: 01-30-2012, 07:29 AM
  5. Planning/strategy?
    By Bryan021 in forum Access
    Replies: 4
    Last Post: 04-18-2011, 11:40 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