Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511

    Unanswered: Appropriate Field Header Names

    I have an upcoming project relating to life insurance which will be comprised of an Access back-end / Excel front-end.

    Currently all of the data is housed in Excel worksheets. The volume of data is going to increase so I plan to move it into Access and query it from my Excel models when calculations need to be run.

    The catch is that my Excel skill level is reasonable but my Access skill level is novice, which means that lots of Access questions are going to come your way!

    The first components I am planning to move into Access are mortality tables. The mortality tables are currently laid out in Excel with Age field headers and Time (years as integers) record headers. The data itself is the probability of death:
    Code:
    .   59      60      61      ...etc... ---> 99
    1   0.0010  0.0011  0.0012
    2   0.0020  0.0021  0.0022
    3   0.0030  0.0031  0.0032
    4   0.0040  0.0041  0.0042
    ...etc... 
    |
    |
    V
    50
    When I get round to querying this table, I am going to want to return all of the records for a certain age, say 61.
    What would appropriate Access table field names be? Is there a reason I shouldn't just use 59, 60, 61 etc..?

    Cheers
    Last edited by Colin Legg; 04-08-10 at 06:28.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Colin
    Quote Originally Posted by Colin Legg View Post
    The catch is that my Excel skill level is reasonable but my Access skill level is novice, which means that lots of Access questions are going to come your way!
    An opportunoty to pay you back then!

    Something very, very important to remember is that Excel is a spreadsheet application for data analysis. Access is not just the same thing but for more rows. It is a relational database management system. As such, there are very different rules regarding the correct structure of data in database tables.

    The reason I stress this is that it is very, very common for people used to Excel to imagine relational tables should resemble Excel tables. This is not the case and the structure you've shown is a perfect example of this.

    Without more information I would suggest a table design of:
    mortality {code, age, probability}
    The combination of code (I am guessing that column name since it is not in your table) and age are unique and are your primary key. The probability is a non-key attribute of the entity the table represents.

    Although you will pivot the data for viewing and analysis (like you displayed) the correct structure to store it in is very different.

    For a quick overview of the basics of relational database design I strongly recommend you read this:
    The Relational Data Model, Normalisation and effective Database Design

    HTH

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Thanks, Pootle. There's a lot of information on that article which will take some time to digest!

    In design mode I have set up a table like this:
    Code:
    tbl_Mortality
     
    Field Name      Data Type           Description
    Age             Number (integer)    Age
    t               Number (integer)    Time in years
    Qx              Number (double)     Probability of Death
    I *think* that's along the lines of what you've suggested, but I haven't included a primary key just yet - pending a follow-up question.

    There are ~ 20 of these mortality tables in Excel (each from a different source), each laid out on a separate Excel worksheet as shown in post #1. Should they all be combined in the same Access table with an extra field added to identify the source mortality table, like this?
    Code:
    tbl_Mortality
     
    Field Name      Data Type           Description
    Age             Number (integer)    Age of Individual
    t               Number (integer)    Time in years
    Qx              Number (double)     Probability of Death
    Source          Number (integer)    Index of Source Table
    Code:
    tbl_Source
     
    Field Name      Data Type           Description
    Index          AutoNumber
    Source          Text                Description of the Mortality Table
    I assume that's better than having ~ 20 separate tbl_Mortality access tables (one for each source table)?

    In terms of a primary key for tbl_Mortality - which I am guessing is a combination of Source, Age and t - is that something I should have a field set up for? I will only be querying it to retrieve the t and Qx records where Age is X and Source is Y?

    Cheers,
    Last edited by Colin Legg; 04-08-10 at 08:05.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Colin - that is about perfect.

    Now, we get in to a concept about surrogate keys and natural keys. Your Index column is a surrogate key for your natural key (Source). It is optional - it is not required however many people like to use them. There is immense literature on the pros and cons of using a surrogate key on the web so I'll leave it to you if you want to look it up.

    If you retain the design as you have it you must also put a unique index (no NULLs) on the Source Text column. I would recommend you call the Source column in mortality something like "source_index" (just to make it more obvious it relates to the Index column not the Source column in the tbl_SourceTable).

    However, a perfectly valid alternative is to dump the tbl_SourceTable and change the Source column in tbl_Mortality to Text. I've actually just covered this concept here: http://www.dbforums.com/microsoft-ac...ationship.html

    For your PK for tbl_Mortality, make sure the order of columns in the PK is Source, Age, t. This is given your last line - PKs are enforced by B-Tree indexes and this order of columns will best support your query.

    Does this help?

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Thanks, that's great. It's just knowing some of the terms (such as "surrogate" and "natural" keys) so that I can efficiently research them. I think that's all I need for the time being - I'll be sure to come back again if I get confused!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •