Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21

    Relational Database Design Questions

    Hello All,



    I've been asked to create a database for one of our customers. The database will house project information (WBS element numbers, program name, division, cost, etc.). I'm not sure the best way to go about this with the information I have to work with.

    For example:

    One data pull I do (i'll have to pull this on a weekly basis and add it to the access database) will contain the full WBS element number (6 levels) and a bunch of financial data columns as well. To that dataset I'd like to pull from tables in order to add the program name, division and other information specific to that WBS number.

    My research on database design says that it'd be optimal to have a separate table each for programs, names, divisions etc (as opposed to one table with a WBS element number and the division, program, etc.). The WBS structure is such that the division is specified in the 2nd level of the WBS, program name in the 3rd etc.

    The problem is that the tables are related by the WBS number, but the programs table would only go up to 2 levels, whereas the data table would contain the full WBS number. I'm assuming I can't define a relationship between the two tables such that the first 14 characters of the WBS element in the programs table match the first 14 characters of the full WBS element in the data table, correct?

    If that's true, is it also correct that in this case I must create one table with all the full WBS elements and the corresponding program names, divisions etc.

    Thanks for all your help! I'm sure you can tell I'm relatively new to both access and relational databases in general. Up to this point, all my database needs have been handled in excel and I haven't had the need to create a database with more than 15,000 records.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    You will have to analyze your data and see how all of the data relates. You will then apply the rules of normalization to come up with an appropriate table structure.

    First I have no idea what a WBS element is. Could you explain?

    Can a project have many WBS elements associated with it?

    and a bunch of financial data columns
    What kind of financial data, i.e. types? Is the financial data related to a WBS element or to the project directly? Is financial data recorded multiple times?

    What is a program?
    Are programs related directly to the project or to the WBS element?
    Can there be many associated programs?

    How do the divisions fit in with the project?

  3. #3
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Thanks for your response. I'll read up on your link on normalization.

    Let's just say that for my puposes a WBS element is a project number and each "project" is associated with it's own unique WBS element.

    Sorry for my poor explanation earlier.

    To simply things, let's say that my customer has many different divisions. Each division has many different programs and each program has many different projects.

    Information is organized by a WBS element #. For simplicity let's say we have a 4-Level WBS element # with the format as follows:

    wwww.xxxxx.yyyyy.zzzzz

    The first level (w's) is a unique idenfier for the organization, the x's a unique identifier for the division, the y's for program and z's for that specific project. Does that make sense?

    All projects within a program will have the same first three levels. All programs within a division will have the same first two levels....etc.

    My customer pulls data every week into an excel spreadsheet and I wrote a VBA procedure to format that data into the format they need for the database. That data consists of a full (all levels) WBS element number followed by the "financial data" I mentioned earlier. That data is all numbers (budgeted amount, costs incurred, etc.). I figured that that data pull alone would make up one of the tables in my database, and I'd have another table for programs, another table for divisions etc.

    Hopefully that's more clear. Thanks for all your help.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Information is organized by a WBS element #. For simplicity let's say we have a 4-Level WBS element # with the format as follows:

    wwww.xxxxx.yyyyy.zzzzz
    Since this WBS element is effectively constructed based on the customer, division, program and project it would be considered as a calculated value and thus not stored in the table, but you would store the portions that are used to construct it, but it is more important at this point to get the table structure set up, so...

    To simply things, let's say that my customer has many different divisions. Each division has many different programs and each program has many different projects.
    From the above a customer has many divisions; this describes a one to many relationship

    tblCustomers
    -pkCustomerID primary key, autonumber
    -txtCustomerName
    -txtCustomerElement (your wwww)

    tblCustomerDivisions
    -pkCustDivID primary key, autonumber
    -fkCustomerID foreign key to tblCustomers, must be a long number integer datatype field to match the autonumber
    -txtDivisionName
    -txtDivisionElement (your xxxxx)

    Since a division can have many programs...

    tblCustomerDivisionPrograms
    -pkCustDivProgramID primary key, autonumber
    -fkCustDivID foreign key to tblCustomerDivisions
    -txtProgramName
    -txtProgramElement (your yyyyy)

    A program can have many projects

    tblCustomerDivisionProgramProjects
    -pkCustDivProProjID primary key, autonumber
    -fkCustDivProgramID foreign key to tblCustomerDivisionPrograms
    -txtProjectName
    -txtProjectElement (your zzzzz)


    The above is just a rough structure and I do not know to which entity the financial data is associated, so you will have to provide more info on that

  5. #5
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Thanks again, that's very helpful and should give me enough to get started.

    One more question, though.

    Which is most beneficial?

    Adding calculated columns to my weekly data pull (the one with the full WBS element followed by financial number columns) to show the first, 2nd and 3rd WBS levels, or adding a separate table entirely showing each WBS element and it's associated tables.

    Thanks!

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I think calculated fields in a query (or on a form or report) would be best, but how best to display the WBS is really up to your requirements

    ...or adding a separate table entirely showing each WBS element and it's associated tables
    Adding another table with duplicate information is not the way to go and would violate good database practices. Again, a query that pulls the info you need is the way to go.

    As to the financial data, we still need to get a better understanding of what financial data you have and to what entity (customer, division, program or project) it relates

  7. #7
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Unfortunately, the only way I can get the financial data is to run a report in another program, save it to excel and run a VBA script in excel to format the data so that it's ready to be uploaded to access. I wish I could query the data from the database directly, but that's not happening.

    So my starting point is always pulling the report with the full WBS element and the financial data. That financial data is tied only to the last (lowest, zzzzz in my example) level of the WBS element.

    My suggestion earlier was to simply expand the VBA code in excel to create extra columns for the 1st, 2nd, 3rd and 4th levels of the WBS element and then exporting that dataset to Access.

    The financial data consists of columns such as "planned costs", "available costs", "assigned costs", "obligations", etc. This data is only tied to the lowest level fo the WBS element and the only way I can bring in this data is through the report pulling process I mentioned earlier. Given those constraints, does it make sense to simply revise my VBA code to create extra columns in my data pull and use that dataset as a table in my access database?

    Thanks for your help!

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    "planned costs", "available costs", "assigned costs", "obligations",
    Technically speaking these are just types of financial data and thus should be records not fields in a table.

    tblFinancialDataTypes
    -pkFinDataTypeID primary key, autonumber
    -txtFinDataTypeName

    That financial data is tied only to the last (lowest, zzzzz in my example) level of the WBS element.
    I assume that the above means project.

    That suggest that you have many pieces of financial data related to a project (a one-to-many relationship)

    tblProjectFinancialData
    -pkProjFinDataID primary key, autonumber
    -fkkCustDivProProjID foreign key to tblCustomerDivisionProgramProjects
    -fkFinDataTypeID foreign key to tblFinancialDataTypes
    -currAmt
    -dteFinData (a date field is probably in order)


    You can link to an Excel spreadsheet or import an Excel spreadsheet as a table in Access. You would just need to run some append queries to move the data into the table structure I have presented.

    Once the data is in the table structure, you can dispose of the linked/imported table.

    From there you can use queries to bring the data together the way you want.

  9. #9
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21

    tblProjectFinancialData
    -pkProjFinDataID primary key, autonumber
    -fkkCustDivProProjID foreign key to tblCustomerDivisionProgramProjects
    -fkFinDataTypeID foreign key to tblFinancialDataTypes
    -currAmt
    -dteFinData (a date field is probably in order)
    Thanks again. I'm starting to see how the tables fit together but I'm confused on the primary key/foreign keys. In the above example, the foreign key to tblCustomerDivisionProgramProjects is set as fkkCustDivProProjID, which is the full WBS element number. However, the primary key for tblCustomerDivisionProgramProjects established in one of your earlier posts as autonumber.

    I feel like I'm missing or misunderstanding something completely here. I'm confused about the purpose of autonumber. Doesn't the primary key for the tblCustomerDivisionProgramProjects have to be the full WBS element number as well? How exactly does that work?

    If you can expound on that, I think I can create an example table, establish the relationships and see how it works.


    You can link to an Excel spreadsheet or import an Excel spreadsheet as a table in Access. You would just need to run some append queries to move the data into the table structure I have presented.

    Once the data is in the table structure, you can dispose of the linked/imported table.

    From there you can use queries to bring the data together the way you want.

    This is something I'll have to figure out how to do. The report I pull weekly formats the data in the following way:

    WBS element || planned_costs || available_costs || assigned_costs

    The wbs element is followed by each data type and the values for each make up one record. It sounds like from your example below, the data needs to be imported to the database to look more like...

    pkProjFinDataID || fkkCustDivProProjID || fkFinDataTypeID || currAmt

    The difference being that each WBS element will have several records based on datatype. I understand how that's more efficient, but converting the canned report I'm given to this format seems like it would difficult.

    Is there a simple way to automate the data and format it that way?

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The primary key of all tables I have shown are autonumber. The foreign keys are long number integer fields. The primary key-->foreign key fields form the relationships between the tables. Neither the primary nor foreign key fields have anything to do with your WBS element.

    The WBS element field shown in each table is just a portion of the entire WBS element. When you join the relevant tables in a query, you would construct the entire WBS element number wwww.xxxxx.yyyyy.zzzzz on the fly by concatenating the fields I have identified as the element fields:

    txtCustomerElement & "." & txtDivisionElement & "." & txtProgramElement & "." & txtProjectElement

    This is something I'll have to figure out how to do. The report I pull weekly formats the data in the following way:

    WBS element || planned_costs || available_costs || assigned_costs

    The wbs element is followed by each data type and the values for each make up one record. It sounds like from your example below, the data needs to be imported to the database to look more like...

    pkProjFinDataID || fkkCustDivProProjID || fkFinDataTypeID || currAmt

    The difference being that each WBS element will have several records based on datatype. I understand how that's more efficient, but converting the canned report I'm given to this format seems like it would difficult.

    Is there a simple way to automate the data and format it that way?
    You are correct in your understanding. Running a correctly setup append query should do the trick. You would only set up the append query once as long as you keep the column headers in the imported table the same with each import and provided you have no new financial data tied to new customers/divisions/program/projects.

    How do you plan on handling new customers/divisions/program/projects? If you plan on setting these up prior to each import, then the query to append the financial data from the imported spreadsheet should run fine.

    If you plan on using the imported data in order to create new customers/divisions/program/projects records, then that will take 4 additional append queries that must be executed in sequence before the financial data is appended. This can be automated through VBA (Visual Basic for Application) code.

  11. #11
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    The primary key of all tables I have shown are autonumber. The foreign keys are long number integer fields. The primary key-->foreign key fields form the relationships between the tables. Neither the primary nor foreign key fields have anything to do with your WBS element.
    Ok, this is where I'm getting confused. It wasn't well explained in my Access for dummies book. I feel like I need to go through another book to understand some of this.

    My impression has been that the primary key of one table must match the foreign key of another, but I'm not seeing that in your example or it's going over my head. Say the primary key in the tblCustomerDivisionProgramProjects table is set to autonumber (1, 2, 3, 4 etc.). I create a foreign key field in the tblProjectFinancialData table called fkkCustDivProProjID. What exactly is populated in this field? You say it's a long number integer field. Do I populate it or does Access do it. Both the primary key (being autonumber) and the foreign key in this case seem like arbitrary numbers to me, so I don't understand what establishing the relationship does.

    In my understanding, I always assumed that the relationships between the tables would be established by assigning the primary and foreign keys to the WBS element. I feel like I'm looking at establishing relationships between database tables through the same lens that I would do a join in SQL, but these are completely different concepts? I'm fairly competent with SQL but the actual design of the database is another story.

    Obviously, I'm missing a big concept here. Your continued help is much appreciated. If you think it'd be a better idea for me to purchase a book, any recommendations would be appreciated. I read good reviews on "Database Design for Mere Mortals" by Michael J. Hernandez. Otherwise, hopefully there's just one small thing I'm not getting and it'll click with more help from you. Thank you.

  12. #12
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    My impression has been that the primary key of one table must match the foreign key of another, but I'm not seeing that in your example or it's going over my head. Say the primary key in the tblCustomerDivisionProgramProjects table is set to autonumber (1, 2, 3, 4 etc.). I create a foreign key field in the tblProjectFinancialData table called fkkCustDivProProjID. What exactly is populated in this field? You say it's a long number integer field. Do I populate it or does Access do it. Both the primary key (being autonumber) and the foreign key in this case seem like arbitrary numbers to me, so I don't understand what establishing the relationship does.
    If the value of the primary key for a record in tblCustomerDivisionProgramProjects is 1, all related records in tblProjectFinancialData will have the value of 1 in the foreign key field, so you are right and it is this relationship that joins the two tables together. In terms of populating it, you would use a form (based on tblCustomerDivisionProgramProjects) and within that form you would have a subform (based on tblProjectFinancialData). When you put the subform in the main form Access will automatically link the two forms based on the relationships in the relationship window. When you add a record to the main form and then move to the subform, Access will populate the foreign key field's value such that it equals the value of the primary key in the main form.

    I always assumed that the relationships between the tables would be established by assigning the primary and foreign keys to the WBS element
    In most cases it is recommended that the value of the primary key has no significance to the user (as is the case for your WBS element). The key is there for 2 reasons, 1 to unique identify the record and 2 to provide the ability to make the join to related tables.

    I feel like I'm looking at establishing relationships between database tables through the same lens that I would do a join in SQL, but these are completely different concepts
    When you create a query Access transcribes the relationships from the relationship window to the query window for you thus saving you the trouble of making the joins in the query window.

  13. #13
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    If the value of the primary key for a record in tblCustomerDivisionProgramProjects is 1, all related records in tblProjectFinancialData will have the value of 1 in the foreign key field, so you are right and it is this relationship that joins the two tables together
    This makes sense to me. What I'm wondering is, how does Access automatically determine which records are related to assign a value of 1? I'd just like to get a better understanding of how Access works.

    Thank you,
    Mike

  14. #14
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You would establish the primary key-->foreign key relationship in the relationship window. From there, when you create forms, your main form would be based on the table (with the primary key) that forms the one side of the one-to-many relationship and your subform would be based on the table making up the many side of the relationship. When you drag the subform into the main form, Access uses the relationship you created to join the subform to the main form. You would then enter a record in the main form (if you are using an autonumber primary key, the value increments) and then as you migrate from the main to the subform, Access uses the relationship to populate the control corresponding to foreign key with the value of the primary key of the record just created in the main form.

  15. #15
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    I'm back with a few more questions.

    1. I'm still partly confused about the wbs element table structures and heres why.

    Here is a sample project number (completely made up)

    00001.00002.00003.00004

    As stated before, the 00001 corresponds to the customer, the 00002 to the division, the 00003 to the program and the 00004 to the project. One thing that I would like to make clear is that while the first level number represents only one customer (meaning 00001 only defines one customer) the other levels do not. For example, there may be multiple divisions assigned to "00002." You can only recognize the division by the inclusion of the first level as well.

    This may be true for the third level as well. There may be many programs whose third level element is "00003" but that particular program is only defined by the whole wbs element number up to that level, 00001.00002.00003.

    Based on this information, would you change the structure we laid out before?

    2. Another complexity in this matter is that some of the full project numbers that come in through my weekly data pull are inaccurate. While they are supposed to conform to the structure I presented to you earlier, there are often setup incorrectly. For this reason, there exists a need to have an overrides table.

    The override table would contain the false project number and a corrected number. I'm not sure how I'd include this and connect it to the rest of the data. Does this make sense?

    3. Also, at the project level I need to assign additional information such activities, applicants, etc. This value would be something I'd find in an Applicants/Activities table that has a finite number of entries. Those tables would be used for looking up values only.

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

Similar Threads

  1. Need to hire out relational database design
    By janakybrent in forum Access
    Replies: 5
    Last Post: 02-19-2016, 05:11 AM
  2. Learning Access - Design Questions
    By learning_access in forum Database Design
    Replies: 2
    Last Post: 02-15-2011, 09:13 AM
  3. Relational database for mice breeding
    By shiphtfour in forum Database Design
    Replies: 19
    Last Post: 12-16-2010, 10:01 PM
  4. Relational Database/query help
    By Champin4 in forum Queries
    Replies: 7
    Last Post: 07-14-2010, 08:49 PM
  5. Best Design for Multiple Y/N Questions on each record
    By DanielHochman in forum Database Design
    Replies: 0
    Last Post: 07-20-2009, 02:51 PM

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 - Senior Forums