Results 1 to 8 of 8
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Single Field Table Design

    Hi. I have been fighting with myself over this design question, Is a single field table valid and of good design?

    The data I'm working with spans over 130 years, things change. Kitchener, ON. was once Berlin ON., Brant county didn't exist, Crysler Farm battlefield is underwater, political ridings boundaries (in ON., three levels), census data boundaries, etc., all change. We (mankind) also have many different systems labeling the same ground with different boundaries (that change).

    I'm trying to create a database which records these things (locations) though time. So for a "place" it will could have


    a name (one or more with To/From dates)
    a population (zero or more census with dates)
    a location (Geocoded maybe, an address maybe with To/From dates)
    be located in many different regions with To/From dates
    may have a control location which could change (Capital) with To/From dates

    Now I'm also thinking that there is no difference between Point, District, City, Zone, County, Region, State, and Country. They are all man defined, have a title (name), have a position in the hierarchy, and can change over time. Doing a bunch of tables that are nearly identical feels wrong, especially when some (most) need to be skipped. Combining them into one with a couple of lookup tables defining their roles, feels better but causes recursive calls.

    Whenever I place fields in the Places table, the temporal time part or the number of nulls pulls it out. So in my mind I see a Places table as a single field table using a auto number, creating a unique PK. It would have relationships (one to many or many to many) to these tables which would have the PlaceID as a FK, a To/From date fields plus the other stuff.

    So does this sound right??? Is there a better (another) way that I'm missing? I could be totally wrong (I have been before), but this is why I'm asking.

    Thanks for looking
    Western_Neil

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    single field table is valid if you are tracking the pets youve owned. yes.
    But if you are going to track cities/counties, no. Pretty much everything has multiple properties ,hence multiple fields, esp. for relational db.
    Unique PK are great, Places always have a city,state,county..so they will never be a single field table.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    W_Neil,

    This seems to be a follow-on/follow-up to posts from 2016.
    https://www.accessforums.net/showthr...731#post326731
    https://www.accessforums.net/showthr...488#post324488

    Has there been any change in requirements or additional information in approach to design?

    Have you tried various table designs/configurations with some sample output requests to see if one/some is better than another?

    Mocking up some outputs/reports can serve to clarify things--sometimes.

    What's new since previous threads/efforts?

  4. #4
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Hello guys. Boy are you good. Yes it’s the same project which really proves I’m not a student and some of the rabbit holes are deep.
    The ER diagram has change, but the concepts are still the same. Looking at it i now see that my PLACE area is starting to look like my UNIT area. Hmmm is this me just repeating bad design decisions?? In both areas I’m getting a central table that wants to collapse to a Single Field Table, with recursive calls to its self. In both cases I get there because to the temporal change effect along with the outlaying tables being nearly identical in structure. My layman training says that is not normalized so I collapse them.

    ranman256 is right, and as I said before I have started there. It’s when I starting looking a the changes over time (temporal effect), that I start split the stuff, and number of null data points.

    I have been working on cleaning and formatting the data for import (small bit). In doing so I have been getting confused by the records with only a PK, but it has been working and I can see it should work. But when I’m getting confused, I’m wondering if the structure is right.

    I have had a “Professional” check the structure and he was okay with it, but my gut still isn’t. I have had an index problem with linking the changes into 1 table, and that tells me a lot of small tables isn’t answer.

    Since this my first project in Access, (I have done Cobol in a RDBMS), I don’t want to kill it by a bad design. So the question I guess would be, Is this a okay way of doing this?

    Of course i want F-35 at a Cessna price.

  5. #5
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Thanks to sanfu https://www.accessforums.net/member.php?u=8689 and kluaoha https://www.accessforums.net/member.php?u=53896 in message Append Query for a Table with a Single Field https://www.accessforums.net/showthread.php?t=67333 single field tables can be a pain. Better to add another field of anything even if it’s not for public use.

  6. #6
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    To make things easier here is where I'm at nowClick image for larger version. 

Name:	Places.jpg 
Views:	16 
Size:	76.7 KB 
ID:	32544

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This is cross posted https://www.mrexcel.com/forum/micros...eld-table.html
    Western_Neil, you need to read this to understand why this can result in a reduction in answers, and what this does to those trying to help free gratis.
    I read 3 and 4 here and wonder how I didn't make the connection earlier. Must be from trying to help (@ Mr. Excel) while traveling.


  8. #8
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Wrote a long response, but timed out. It's late so ...

    We agree that a Single Field Table populated by a Autonumber is a Dumb, Crazy, and Stupid idea.
    so what I'm looking at is:
    ID Title Abbv Boss TypeFK LevelFK Fr To
    1 Alberta AB 3 3 2 1905
    2 Ontario ON 3 3 2 1791
    3 Canada Cdn 2 1 1867
    4 Kitchener 2 4 3 1916
    5 Ireland Ire 2 1 1922
    6 Ireland Ire 8 2 3 1922
    7 Northern Ireland NI 8 2 3 1922

    Note that for most of these, over time, have different titles. The boundaries issue I'm trying avoid.
    Note that Type and Level are there to combine several table together and aid in the sorting of the records.

    Thanks Mircron Thanks
    Last edited by Western_Neil; 02-07-2018 at 01:44 AM. Reason: Name

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

Similar Threads

  1. Single Field Combo Box to Add Source Table.
    By raychow22 in forum Forms
    Replies: 4
    Last Post: 09-01-2017, 10:25 AM
  2. Append Query for a Table with a Single Field
    By Kluaoha in forum Queries
    Replies: 4
    Last Post: 08-01-2017, 03:09 PM
  3. Replies: 14
    Last Post: 01-08-2016, 07:09 PM
  4. Replies: 1
    Last Post: 02-16-2013, 09:05 AM
  5. Replies: 1
    Last Post: 05-17-2011, 05:19 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