A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Trying to wrap my head around splitting up & combining tables



 
 
Thread Tools Display Modes
  #1  
Old July 2nd, 2009, 03:11 PM posted to microsoft.public.access.tablesdbdesign
Monet 138
external usenet poster
 
Posts: 7
Default Trying to wrap my head around splitting up & combining tables

We have a database for our valves & hydrants for which I'm attempting to
improve in many areas. One such area is the location field can contain a lot
of different information such as Area/Town, Contract, Primary Street,
Secondary Street, County, Misc Info, RR-Xing, Stream-Xing. A lot of the
individual pieces of these records are common and show up on many records,
but needs to be displayed as a single string of text for reports.

Here is my guess on how it should be setup but I could really use some
advice if I'm going about this incorrectly or inefficiently.

tbl_Location: containing, ID, Misc Info, RR-Xing and Stream-Xing and links
to the following tables
tbl_Area: ID, Area
tbl_Contract: ID, Contract
tbl_Street (2 links): ID, Street
tbl_County: ID, County

Setup a form for the tbl_Location table using combo-boxes from other tables
and text & check-boxes for the rest. Then create a column in the Valve &
Hydrant tables for location that creates a single text string (concatenation)
of the various columns from tbl_Location.

Hope I explained that well enough. Thanks in advance for the help.

-- "Imagination is more important than Knowledge. Knowledge is limited,
Imagination encircles the world." ~Albert Einstein
  #2  
Old July 2nd, 2009, 03:48 PM posted to microsoft.public.access.tablesdbdesign
Roger Carlson
external usenet poster
 
Posts: 824
Default Trying to wrap my head around splitting up & combining tables

Personally, I'm leery of giving specific advice on table design. What looks
good in a post, might not match your actual business rules. The
relationships between the tables are even more important than the table
designs themselves, and you haven't included any information about their
relationships here.

I suggest reading "What Is Normalization?" and working through some of the
tutorials he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=238.

They give you a step-by-step rationale for database development, and you'll
know you've got them designed right.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Monet 138" wrote in message
...
We have a database for our valves & hydrants for which I'm attempting to
improve in many areas. One such area is the location field can contain a
lot
of different information such as Area/Town, Contract, Primary Street,
Secondary Street, County, Misc Info, RR-Xing, Stream-Xing. A lot of the
individual pieces of these records are common and show up on many records,
but needs to be displayed as a single string of text for reports.

Here is my guess on how it should be setup but I could really use some
advice if I'm going about this incorrectly or inefficiently.

tbl_Location: containing, ID, Misc Info, RR-Xing and Stream-Xing and links
to the following tables
tbl_Area: ID, Area
tbl_Contract: ID, Contract
tbl_Street (2 links): ID, Street
tbl_County: ID, County

Setup a form for the tbl_Location table using combo-boxes from other
tables
and text & check-boxes for the rest. Then create a column in the Valve &
Hydrant tables for location that creates a single text string
(concatenation)
of the various columns from tbl_Location.

Hope I explained that well enough. Thanks in advance for the help.

-- "Imagination is more important than Knowledge. Knowledge is limited,
Imagination encircles the world." ~Albert Einstein



  #3  
Old July 2nd, 2009, 04:05 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Trying to wrap my head around splitting up & combining tables

?!

You have a "field" which can contain apples, oranges, mini-vans, pencils,
and elephants?!

A common database design principle is "one fact, one field". I'm with
Roger, your design would probably benefit from a review of "normalization"
and "relational database design".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Monet 138" wrote in message
...
We have a database for our valves & hydrants for which I'm attempting to
improve in many areas. One such area is the location field can contain a
lot
of different information such as Area/Town, Contract, Primary Street,
Secondary Street, County, Misc Info, RR-Xing, Stream-Xing. A lot of the
individual pieces of these records are common and show up on many records,
but needs to be displayed as a single string of text for reports.

Here is my guess on how it should be setup but I could really use some
advice if I'm going about this incorrectly or inefficiently.

tbl_Location: containing, ID, Misc Info, RR-Xing and Stream-Xing and links
to the following tables
tbl_Area: ID, Area
tbl_Contract: ID, Contract
tbl_Street (2 links): ID, Street
tbl_County: ID, County

Setup a form for the tbl_Location table using combo-boxes from other
tables
and text & check-boxes for the rest. Then create a column in the Valve &
Hydrant tables for location that creates a single text string
(concatenation)
of the various columns from tbl_Location.

Hope I explained that well enough. Thanks in advance for the help.

-- "Imagination is more important than Knowledge. Knowledge is limited,
Imagination encircles the world." ~Albert Einstein



  #4  
Old July 2nd, 2009, 07:51 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Trying to wrap my head around splitting up & combining tables

To add to the excellent advice already given


You seem fluent in a lot of the database terminology yet scrambled on the
underlying data organization.

I'd start by shutting off the computer an listing the entities that you want
to track, and the attributes of those entities that you want to record.

Certainly your hydrants are an entity that you are tracking. Locations
might be entities (especially if the same location has many hydrants) or they
might be just attributes of the hydrants.

Any type of attribute where you have that "answer" for nearly every record
(e.g. County) should probably be a field for that record. Where such is
not the case (e.g. stream crossing) such should probably be stuffed into a
more general type field.

Then design your table structure. Don't even think about that other
database stuff (checkboxes, concentating, combo boxes etc. ) until you've
finished that.

  #5  
Old July 6th, 2009, 03:02 PM posted to microsoft.public.access.tablesdbdesign
Monet 138
external usenet poster
 
Posts: 7
Default Trying to wrap my head around splitting up & combining tables

Thanks for all the help, I learned a lot reading those documents.

I have two more questions the second of which depends on the answer to the
first.

1. The smaller I can keep the file size the better. So for fields such as
STREET (where each can show up on multiple entries and many entries can have
two STREET listings), would it be better to have a separate table holding
those and then link that to the LOCATION table or just have it stored
directly in the LOCATION table?

2. Many entries in the LOCATION table can have two STREET entries (listed
as Primary & Secondary). If STREET should be stored in its own table to help
reduce file size, how do I link the same table to two different entities
(Primary & Secondary) on the LOCATION table?

Example: (Field A_Entry - Field B_Entry - Field C_Entry)
Valve_1 - Primary_A Street - Secondary_B Street
Valve_2 - Primary_A Street - Secondary_C Street
Valve_3 - Primary_C Street - Secondary_A Street

Thanks again in advance.
--
"Imagination is more important than Knowledge. Knowledge is limited,
Imagination encircles the world." ~Albert Einstein

  #6  
Old July 6th, 2009, 03:36 PM posted to microsoft.public.access.tablesdbdesign
Roger Carlson
external usenet poster
 
Posts: 824
Default Trying to wrap my head around splitting up & combining tables

Address tables are a thorny problem in normalization terms. Sometimes I do
them one way, sometimes another.

Sure, you could have a separate table for Streets because storing the street
multiple times can introduce redundancy with all the problems that entails.
However, by that same reasoning, we should have a FirstName table and a
LastName table, because there can be multiple "Roger"s in the database and
also multiple "Carlson"s. However, having a FirstName and LastName table
has limited utility even though it adheres to strick normalization rules. I
think a Streets table falls into the same category.

I will often have City, State, and Zip tables. However, the relationships
here are complex too. Both Michigan and Minnesota have a city called Grand
Rapids. It's also possible for a single city to be in two states. Some
cities have multiple zip codes in them and some zip codes span multiple
cities. Working out the relationships so only the correct city, state and
zip combinations can be chosen is quite complex.

One step down from full normalization is to just have separate City, State,
and Zip tables that are used as lookup tables to keep spelling errors down.
Such a system relies on the user to correctly select the proper combination
of them. Humans can be quite good at this, and sometime you just have to
trust them. However, I generally don't extend this to Streets. I usually
keep the entire address in one field.

Now, if a person or business can have multiple addresses, I will create an
Address (or possibly Location) table because there is a definite one-to-many
relationship there.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L



"Monet 138" wrote in message
...
Thanks for all the help, I learned a lot reading those documents.

I have two more questions the second of which depends on the answer to the
first.

1. The smaller I can keep the file size the better. So for fields such
as
STREET (where each can show up on multiple entries and many entries can
have
two STREET listings), would it be better to have a separate table holding
those and then link that to the LOCATION table or just have it stored
directly in the LOCATION table?

2. Many entries in the LOCATION table can have two STREET entries (listed
as Primary & Secondary). If STREET should be stored in its own table to
help
reduce file size, how do I link the same table to two different entities
(Primary & Secondary) on the LOCATION table?

Example: (Field A_Entry - Field B_Entry - Field C_Entry)
Valve_1 - Primary_A Street - Secondary_B Street
Valve_2 - Primary_A Street - Secondary_C Street
Valve_3 - Primary_C Street - Secondary_A Street

Thanks again in advance.
--
"Imagination is more important than Knowledge. Knowledge is limited,
Imagination encircles the world." ~Albert Einstein



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.