Results 1 to 8 of 8
  1. #1
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169

    Want to know if this looks like a good database design

    Table 1: Business
    column 1: bus_id PK
    column 2: busabbrev
    column 3: busdesc

    Table 2: Line
    column 1: lob_id PK
    column 2: lobabbrev
    column 3: lobdesc

    Table 3: State
    column 1: st_id PK
    column 2: stabbrev

    Table 4: Products


    column 1: prod_id PK
    column 2: prodname
    column 3: proddesc (memo)

    Table 5: Measure
    column 1: mea_id PK
    column 2: meaname
    column 3: meadesc (memo)
    column 4: condition
    column 5: meacd
    column 6: submea

    Table 6: Contact
    column 1: contact_id PK
    column 2: contact
    column 3: phone
    column 4: email

    Table 7: Mem
    column 1: mem_id PK
    column 2: memfirst
    column 3: memlast
    column 4: memdob
    column 5: memstr
    column 6: memcty
    column 7: memst
    column 8: memzip
    column 9: memphone

    Table 8: DataTable
    column 1: Data_ID PK
    column 2: busabbrev (from above table)
    column 3: lobabbrev (from above table)
    column 4: stabbrev (from above table)
    column 5: prodname (from above table)
    column 6: meaname (from above table)
    column 7: contact (from above table)
    column 8: mem_id (from above table)
    column 9: target
    column 10: reached
    column 11: converted
    column 12: inputdate
    column 13: timestamp

    I am inquiring because I had the Datatable with just the ID's or the PK's from each table and nothing lined up right when querying. But when I used items from each table that we want to display on, it did work. and I actually join like stabbrev in datatable to stabbrev in state table.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Seems you need better understanding of PK/FK. If you aren't using values of the designated PK fields as FK then should not be designated as PK.

    Why did queries not 'line up'? Did you set lookups with alias in tables on the FK fields? I NEVER set lookups in tables. http://access.mvps.org/access/lookupfields.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    So you think I could do it like this and it makes better sense?



    Table 1: Business
    column 1: busabbrev PK
    column 2: busdesc

    Table 2: Line
    column 1: lobabbrev PK
    column 2: lobdesc

    Table 3: State
    column 1: stabbrev PK

    Table 4: Products
    column 1: prodname PK
    column 2: proddesc (memo)

    Table 5: Measure
    column 1: meaname PK
    column 2: meadesc (memo)
    column 3: condition
    column 4: meacd
    column 5: submea

    Table 6: Contact
    column 1: contact PK
    column 2: phone
    column 3: email

    Table 7: Mem
    column 1: mem_id
    column 2: meaname PK
    column 3: memfirst
    column 4: memlast
    column 5: memdob
    column 6: memstr
    column 7: memcty
    column 8: memst
    column 9: memzip
    column 10: memphone

    Table 8: DataTable
    column 1: Data_ID PK
    column 2: busabbrev (from above table)
    column 3: lobabbrev (from above table)
    column 4: stabbrev (from above table)
    column 5: prodname (from above table)
    column 6: meaname (from above table)
    column 7: contact (from above table)
    column 8: target
    column 9: reached
    column 10: converted

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Descriptive text field can be PK. Although many experienced developers argue against text fields as PKs because they index slower. Having said that, I have used text field as PK without issue. An advantage is simpler combobox setup. Also, queries can be simpler. The DataTable already has the primary info from each 'lookup' table so unless other related info is needed, no joins are required.

    Should the field name in Mem table be memname?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    Yes it should be memname.

    I wish I could just have the one table but I need the state, year, month which I did not include in my example because the end user might want to filter by 2013 in the front-end application that has listboxes instead of trying to filter through the 389k row table itself. The end-user cannot query. They can only use the prebuilt forms and after I switched the front-end to Tableau, I found that my program name for NY does appear but it appears with a different contact person. The 4th row finally shows the right contact person. I know that is because of how the joins are in Tableau. At that time I had in the main table mth_id, year_id, bus_id and on and on. Those were my primary keys in those feeder tables. But I changed it to the example I first posted and it looks good in Tableau but I am thinking it looks wrong for a table design and if I am going to have the datatable(maintable) with things like Dec, 2014, Sheila Edwards(contact) then why not make Mth the PK and get rid of the _id. Why not make Year the PK and get rid of _id. Why not make contact the PK and get rid of contact_id? I just want to make sure that this is a logical design. Because we went from Access to Teradata and Teradata is all messed up. Now we are going to a SQL Server but I still build it all in Access and then will migrate it to SQL SErver

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    People names make poor unique identifiers. Name parts should really be in separate fields: LastName, FirstName.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    Ok. Think I will go back to my original from the very start. It is just when someone tried to do a join in the front-end Tableau things were not mixing correctly. so they must have done a wrong filter in Tableau because the join is correct. it is an inner join from that main table to the feeders so not sure what to tell them. I would have to look at their filter criteria to tell. thanks.

    Table 1: Business
    column 1: bus_id PK
    column 2: busabbrev
    column 3: busdesc

    Table 2: Line
    column 1: lob_id PK
    column 2: lobabbrev
    column 3: lobdesc

    Table 3: State
    column 1: st_id PK
    column 2: stabbrev

    Table 4: Products
    column 1: prod_id PK
    column 2: prodname
    column 3: proddesc (memo)

    Table 5: Measure
    column 1: mea_id PK
    column 2: meaname
    column 3: meadesc (memo)
    column 4: condition
    column 5: meacd
    column 6: submea

    Table 6: Contact
    column 1: contact_id PK
    column 2: contact
    column 3: phone
    column 4: email

    Table 7: Mem
    column 1: mem_id PK
    column 2: memfirst
    column 3: memlast
    column 4: memdob
    column 5: memstr
    column 6: memcty
    column 7: memst
    column 8: memzip
    column 9: memphone

    Table 8: DataTable
    column 1: Data_ID PK
    column 2: bus_id (from above table)
    column 3: lob_id (from above table)
    column 4: st_id (from above table)
    column 5: prod_d (from above table)
    column 6: mea_id (from above table)
    column 7: contact_id (from above table)
    column 8: mem_id (from above table)
    column 9: target
    column 10: reached
    column 11: converted
    column 12: inputdate
    column 13: timestamp

  8. #8
    accedeholdings is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Sep 2014
    Location
    Hackney, South Australia
    Posts
    17
    Your line of code I think is ok but the challenge will be to make it more operational to handle huge amount of data.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-31-2013, 02:50 PM
  2. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  3. Is this a good database design?
    By Someday in forum Database Design
    Replies: 4
    Last Post: 07-22-2012, 06:50 PM
  4. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM
  5. Replies: 6
    Last Post: 08-04-2010, 01:16 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