X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> One Many To Many, Or Several Many To One Tables?, Access 2016    
post Dec 31 2019, 01:03 AM

Posts: 2
Joined: 11-July 19

I'm new to relational DBs, managing a 27 year old DB that began as dBASE, now an MDB geodatabase that drives a popular web map application. The DB isn't exactly 'broken,' but is a complicated mess. If I were hit by a bus tomorrow . . . pity my replacement. New DB records are being populated daily. I expect the DB's scope to continue to expand in ways I cannot foresee. I intend to overhaul the DB to be more efficient on several fronts, such as eliminating reserved chars from table and field names, and normalizing key field names across tables, updating datatypes, etc. I think some restructuring is in order. Apologies if my syntax is off, I don't think in SQL yet.

The primary table contains location (lon/lat) data and unique site IDs (over 9,000) for relates to sourced media attributes for download or viewing via the web app (all public domain or provided by permission; over 15,000 files). Media include documents like scanned maps and reports, A/V files, photos, academic references, etc. that may apply to more than one location. There are tables to relate specific media and references (at least one reference required to justify site inclusion), to the primary table. Media and reference records are separate relate trees, though some media are references. Physical media that are a collection of files, such as a scanned portfolio or contents of a CD, are on their own tree, as well as annual reports dating back to 1899 that contain records for many sites. There are other attributes, such as company or commodity, that span sites and media. Some of these data 'stubs' were created as stand-alone databases, and integrated into the DB.

I'm considering aggregating all references and media relates for location into one table, and would appreciate thoughts before I commit. I don't want to keep putting pots under the leaks in the roof, the time saved documenting each release is alone enough to justify a broad normalization effort. Much less porting to other DBMS.

The unique IDs for location and media records are alphanumeric, references are long integer, without duplication. Company is unique as integer or alphabetic, and commodity is unique alphabetic. I like the human-readable ID's that indicate site location info and type of media data, and I like being able to intuitively query from this knowledge, as do the users. I don't think migrating all tables to a strictly numeric ID is in the cards.

If I were to aggregate all location info through a single look up table, should that table have record of the data tree that is calling for the lon/lat beyond the unique ID? For instance, the user wants to query only annual reports, would this go-between table be the ideal placeholder for isolating these data? Or am I completely off-base and should stick with the tables and queries and forms that are mostly working? I've read the docs on normalization supplied here, but not sure quite where to settle on granularity as far as relating locations in this DB. Your insights appreciated
Go to the top of the page
post Dec 31 2019, 04:34 PM

Posts: 2
Joined: 11-July 19

Short version: I have one table with ~9000 lon/lat locations, and eight tables that relate records of different media types to locations (~15,000 in total). Any given media might have more than one location attribute, and one location may have more than one media attribute. Media records require different fields, so nothing to be done about that. Location IDs are unique, and media filenames are unique.

The relate tables consist of two fields: filename and location_ID,

Question is, should I have eight 'one to many' tables that relate media to locations, or one table relating all media to locations? In other words, would a single relate table be granular enough to be considered best practice?
This post has been edited by kawaisunn: Dec 31 2019, 04:41 PM
Go to the top of the page
Jeff B.
post Jan 1 2020, 09:02 AM

UtterAccess VIP
Posts: 10,354
Joined: 30-April 10
From: Pacific NorthWet

If you are saying you're considering 8 identical tables, each storing data for a different 'aspect', why not use a single table with an 'aspect' field? One problem to the former approach is that everything (almost EVERYTHING) would need to be modified if you ever had seven or nine or (???) 'aspects' to deal with.


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    27th January 2020 - 12:43 AM