Results 1 to 7 of 7
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Naming Convention for multi level organization

    Ok scrapped my first go and trying a database and want to get the second try right.




    I have Houses that report to Chapters, Chapters report to outreach, and outreach reports to the state

    So I will have tables for each tblHouse, tblChapter, tblOutreach,

    but each table will have a lot of the same kind of informatiion; name, address, phone#, Email address etc

    so should I name the fields with refernce to the table?

    Example

    tblHouse
    HouseName
    HouseAddress
    HouseCity
    HouseZip
    HousePhone

    tblChapter
    ChapterName
    ChapterAddress
    ChapterCity
    ChapterZip
    ChapterPhone

    or is that overkill because I will have to refer to the tbl to refer to the field anyway???

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It's often easier and more effective to give the 30,000 ft overview description of the proposed application in plain English when starting design. I would recommend you do not have fields with embedded spaces nor special characters (#) in their names.
    There are several articles in the Database Planning and Design link in my signature. The tutorials from RogersAccessLibrary within the link will lead you through a process to design your tables and relationships(data model), that you can test with some sample data and sample scenarios.
    Good luck with your project.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    From your description I would have one table

    tblEntitities
    EntityPK
    EntityType (House/Chapter/etc)
    EntityName

    EntityAddress
    EntityCity
    EntityZip
    EntityPhone

    and if the relationships are one to many have an additional field to indicate which chapter a house belongs to or outreach a chapter belongs to

    EntityFK - links to EntityPK in the same table for the 'parent'

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    tblHouse: HouseID (autonumeric, PK), ChapterID (Long integer, FK), HouseName, ...
    tblChapter: ChapterID (autonumeric, PK), OutreachID (Long Integer, FK), ChapterName, ...
    etc.

    This structure assumes, a House may belong to only one Chapter, a Chapter may belong to only one Outreach, etc.

  5. #5
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Thanks ArviLaanemets

    But do I need to continue that all the way for each field

    tblHouse: HouseID (autonumeric, PK), ChapterID (Long integer, FK), HouseName, HouseAddress, HouseCity, HouseST, HouseZip, HousePhone, HouseEmail,
    tblChapter: ChapterID (autonumeric, PK), OutreachID (Long Integer, FK), ChapterName, ChapterAddress, ChapterCity, ChapterST, ChapterZip, ChapterPhone, ChapterEmail

    Or can I drop the table name in front of those field names like this

    tblHouse: HouseID (autonumeric, PK), ChapterID (Long integer, FK), HouseName, Address, City, ST, Zip, Phone, Email,
    tblChapter: ChapterID (autonumeric, PK), OutreachID (Long Integer, FK), ChapterName, Address, City, ST, Zip, Phone, Email


    Thaks

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    did you read post#3?

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Tuckejam View Post
    But do I need to continue that all the way for each field
    ...
    Or can I drop the table name in front of those field names like this
    What convention you use for field names is up to you - nothing set in stone there in Access.
    But I'd add a table tblContacts: ContactID (autonumeric, PK), Address, City, ST, Zip, [Phone], [EMail]
    (in case there will always be a single address, single phone, and single e-mail)
    and have only ContactID in tblHouse/tblChapter/etc. as FK.

    In case there may be several contacts of every contact type, I'd have separate tables tblAddresses, tblPhones and tblEMails,
    and then a table like tblUnitContacts: UnitContactID, UnitID, UnitType, ContactID, ContactType,
    where UnitType will have values for House, Chapter, ..., UnitID will be FK to tblHouse, tblChapter, ..., ContactType will have values for adresses, phones and e-mails, and ContactID will be FK to tblAddresses, tblPhones, or tblEMails.
    With this design, no need for any contact info in tables tblHouse, tblChapter, ... at all.

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

Similar Threads

  1. Replies: 3
    Last Post: 11-13-2019, 04:30 PM
  2. The PK and FK field naming convention....
    By DBID10T in forum Database Design
    Replies: 6
    Last Post: 10-25-2019, 05:49 AM
  3. Naming convention question? btn or cmd?
    By RLehrbass in forum Access
    Replies: 7
    Last Post: 09-21-2015, 07:51 PM
  4. I use my own VBA naming "convention"
    By keviny04 in forum Modules
    Replies: 13
    Last Post: 06-05-2015, 03:09 AM
  5. Help with multi-level subtotals
    By Haro-san in forum Reports
    Replies: 2
    Last Post: 08-12-2014, 06:47 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