Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-09-2018, 10:37 PM   #1
Harper
Newly Registered User
Silver Supporter
 
Join Date: Dec 2017
Posts: 17
Thanks: 11
Thanked 0 Times in 0 Posts
Harper is on a distinguished road
Table Design Advice - Yes/No & Notes!

I created this basic database for self-learning and have stumbled on whether "Yes/No" and "Notes" fields should be individual fields in the "Master" table, or reside in their own tables as yes/no selection and notes.

Do the other tables look OK?

Thanks everyone!
Attached Files
File Type: accdb DatabaseHarper.accdb (1.35 MB, 39 views)


Last edited by Harper; 01-10-2018 at 05:28 AM.
Harper is offline   Reply With Quote
Old 01-10-2018, 03:50 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,502
Thanks: 360
Thanked 779 Times in 744 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Table Design Advice

With the yes no Fields, I assume you mean should you have them as individual fields in a table or should you have a table that stores them separately as items you want to identify with a yes/no selection. It's always difficult to sus this one out, it "depends"! If they are going to be instrumental in filtering records for reports and things then probably a separate table is favourite. If it's just a signal to say that all the data has been collected, or some other sort of verification, then in the same table might be best.

One of the problems with having a separate table of yes/no, let's say checkboxes, is how do you display this information on the form? You could have a situation where your user is forced to enter individual data by a combobox for each record not something they would enjoy doing...

I mention this because I covered a solution to this problem in detail on my website here:-

Checklist Required!
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
Harper (01-10-2018)
Old 01-10-2018, 06:00 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,934
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design Advice

Looking at your Relationship tool I see these issues:

1. Tables with duplicate structures. When you have similarly structured tables, the data in all of them needs to go into just 1 table. Instead of a Manager and a Foreman table, you simply need a People table where you store all the information of all people.

2. Tables with just 1 real field of data (autonumbers don't count as real data--they exist for the database itself). You should not have these tables, instead you simply store the actual value in the table you are linking to. That means State and Team go away. Instead of storing their ID's in Master, you would store the values they relate to in Master.

3. Numerated field names. When you feel the need to store data in table or field names (e.g. the year a column is for) it is time to make a new table. Instead of storing that year data in a column name, you make a new table which has a column to hold the year the data is for. I mean, 2018 is here and I have it on good faith 2019 is going to occur in less than 370 days--what then?

I would work on those big issues. Also, don't use non-alphanumeric characters as table/field names. That means no spaces or # symbols in names--it makes coding and querying easier down the line.

plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Harper (01-10-2018)
Old 01-10-2018, 02:38 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Table Design Advice

plog,
re #2 -- those tables exist to control the universe of values for Team and State. They could be single column tables rather than an autonumber + text but they do need to exist otherwise people can enter random characters in the state or team fields rather than one of the defined values. Those two tables would be used as the RowSource for combos.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Harper (01-10-2018)
Old 01-10-2018, 02:50 PM   #5
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,934
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design Advice

Quote:
they do need to exist...
Incorrect. The Row Source of a field in a table/form can be a list which can be used to prevent willy nilly input from users.

Further, if the system is being populated with existing data, the Row Source can be a query that uses the Distinct values that exist in that field of the table.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Harper (01-10-2018)
Old 01-10-2018, 03:28 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Table Design Advice

I don't think we need to get into an argument over this but do you want to maintain long value lists in table design view? I don't. I actually don't want to maintain them at all. I prefer that the user maintain the value lists when there is no program logic associated with them. So in this app, States and Teams would be left entirely under the control of the user. I would give him forms to manage them to avoid the lazy typos that occur when users are allowed to add to a list from the main maintenance form.

In fact, I have a mini-app that I add to all applications that is used to manage simple lookup lists so rather than one table per list, I use one table for all lists but if an app has only a couple of lists to maintain, I don't believe there is any technical objection to using tables rather than value lists to manage them and I believe it is actually better practice.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Harper (01-10-2018)
Old 01-11-2018, 05:31 AM   #7
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,934
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design Advice

Perhaps you just chose your words wrong initially ('need to exist'), but you incorrectly corrected me. I corrected your correction. Do what you want with your tables, even advise him of your method. I stick by my recommendations.

plog is offline   Reply With Quote
Old 01-11-2018, 06:02 AM   #8
AccessBlaster
.
 
Join Date: May 2010
Posts: 868
Thanks: 19
Thanked 234 Times in 224 Posts
AccessBlaster will become famous soon enough AccessBlaster will become famous soon enough
Re: Table Design Advice

Quote:
Originally Posted by Pat Hartman View Post

...In fact, I have a mini-app that I add to all applications that is used to manage simple lookup lists so rather than one table per list, I use one table for all lists but if an app has only a couple of lists to maintain, I don't believe there is any technical objection to using tables rather than value lists to manage them and I believe it is actually better practice.
I like this idea for simple lookups. Please post an example of you're mini-app. Thanks.
AccessBlaster is offline   Reply With Quote
Old 01-11-2018, 07:37 AM   #9
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,502
Thanks: 360
Thanked 779 Times in 744 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Table Design Advice - Yes/No & Notes!

Quote:
Originally Posted by AccessBlaster View Post
I like this idea for simple lookups.
I seem to recall from previous interactions with Pat that we have a similar system. I've been meaning to do a set of YouTube videos about this method of handling lookups.

In view of the interest, I might bring it forward on my list of things I might do one day!

Sent from my SM-G925F using Tapatalk
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 01-11-2018 at 08:43 AM.
Uncle Gizmo is offline   Reply With Quote
Old 01-11-2018, 10:48 AM   #10
AccessBlaster
.
 
Join Date: May 2010
Posts: 868
Thanks: 19
Thanked 234 Times in 224 Posts
AccessBlaster will become famous soon enough AccessBlaster will become famous soon enough
Re: Table Design Advice

Gizmo, yeah it seems like a good way of tidying up some lookup tables.
AccessBlaster is offline   Reply With Quote
Old 01-11-2018, 02:42 PM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Table Design Advice

Here's the application.
Attached Files
File Type: zip TableMaintExample180111.zip (978.4 KB, 33 views)
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-11-2018, 08:11 PM   #12
AccessBlaster
.
 
Join Date: May 2010
Posts: 868
Thanks: 19
Thanked 234 Times in 224 Posts
AccessBlaster will become famous soon enough AccessBlaster will become famous soon enough
Re: Table Design Advice

Thanks for sharing.
AccessBlaster is offline   Reply With Quote
Old 01-11-2018, 09:20 PM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Table Design Advice

You're welcome.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-15-2018, 09:38 PM   #14
Harper
Newly Registered User
Silver Supporter
 
Join Date: Dec 2017
Posts: 17
Thanks: 11
Thanked 0 Times in 0 Posts
Harper is on a distinguished road
Re: Table Design Advice

Quote:
Originally Posted by plog View Post
Looking at your Relationship tool I see these issues:

1. Tables with duplicate structures. When you have similarly structured tables, the data in all of them needs to go into just 1 table. Instead of a Manager and a Foreman table, you simply need a People table where you store all the information of all people.
Hi all!

I followed plog's advice #1 in making 1 table for manager and foreman. I'm a little confused on how to establish a relationship with the Master table?
Attached Files
File Type: accdb DatabaseHarper2.accdb (1.35 MB, 27 views)
Harper is offline   Reply With Quote
Old 01-16-2018, 05:39 AM   #15
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,934
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design Advice

As it stands now, you would bring in 2 different instances of People and JOIN one of them via MangerID and the other via ForemanID. However, you need to fix some things still.

1. Use autonumber primary keys as table IDs. This makes things run more efficiently and releases you from making sure you are creating good ones when you add data. This will require you change the foreign key in tables that relate via those ID fields to numeric data types.

2. You still have numerated field names. See my #3 from before.

plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Harper (01-17-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need design advice gmatriix Forms 3 06-20-2013 04:57 AM
Advice on db design kalmi General 2 07-18-2011 05:00 PM
advice needed for table layout/design for a newbie jas118 Tables 8 06-05-2010 02:49 PM
Question Design Advice LadyDi General 4 03-02-2009 01:54 PM
Advice on table design Fear Naught Tables 1 03-09-2006 01:45 PM




All times are GMT -8. The time now is 05:26 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World