UtterAccess.com
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
> Help Beginner With Db Schema, Access 2016    
 
   
chrmaz
post Feb 11 2019, 02:40 PM
Post#1



Posts: 6
Joined: 11-February 19



Hi,

i am a beginning MS Access user trying to develop a database for a housepainting company. The DB needs to store information about each customer, each job (customers may have multiple jobs), job details, and rudimentary information about the paint used for each job (in case they need to go back in the future to repaint the same facade).

i've drawn up the attached schema and would welcome any comments or critiques. i am using MS Access 2016.

Trying very hard to understand the normalization process. Also, was originally going to add prefixes (tbl, frm, etc) to everything, but is this archaic?

Thank you!



Attached File  DBSketch.png ( 35.29K )Number of downloads: 19
Go to the top of the page
 
DanielPineault
post Feb 11 2019, 03:13 PM
Post#2


UtterAccess VIP
Posts: 6,404
Joined: 30-June 11



Normally, you woud break off fields such as Phone or Email into separate tables to enable multiple entries




tblPhones
CustomerId
PhoneTypeId -> FK to a related table with values like Home, Business, Cell, ...
Phone
PhoneExtension

tblEmails
CustomerId
EmailTypeId -> FK to a related table with values like Personal, Business, ...
Email

You might even wish to break the Address into a separate table as you might deal with people with multiple properties, no?

Category should be FK to a related table with the values.

Stay away from attachments! See http://www.devhut.net/2017/04/20/access-be...shooting-steps/ and http://www.devhut.net/2016/10/03/adding-at...ccess-database/


--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
bakersburg9
post Feb 11 2019, 03:45 PM
Post#3



Posts: 5,359
Joined: 2-November 04
From: Downey, CA


QUOTE
add prefixes (tbl, frm, etc ... archaic....
an alternative to the Leszynski naming convention, I use "t," "q," "r," etc - like tCustomers, tPaintTypes, qJobsByCustomer ... you still know the object is a table, query, form, report, whatever, but you don't have to go over 4 characters when looking at a schema, a group of tables, etc. - it's personal preference plus if you're looking at SQL from a query, it's less messy and easier to read
This post has been edited by bakersburg9: Feb 11 2019, 03:46 PM
Go to the top of the page
 
chrmaz
post Feb 11 2019, 03:54 PM
Post#4



Posts: 6
Joined: 11-February 19



Thanks bakersburg9, that is good idea. Access already can categorize objects, so i'm not sure when i'll need the prefix as an identifier. i did some googling on the subject and i can't exactly tell if it is an archaic practice or not. It seems like a lot of developers have dropped it.
This post has been edited by chrmaz: Feb 11 2019, 03:57 PM
Go to the top of the page
 
tina t
post Feb 11 2019, 04:11 PM
Post#5



Posts: 5,746
Joined: 11-November 10
From: SoCal, USA


QUOTE
You might even wish to break the Address into a separate table as you might deal with people with multiple properties

to add my own two cents to Daniel's comment, suggest you leave the address info in tblCustomers, as the address where the customer resides or does business - where you contact him/her/them. suggest you add a tblProperties, to list the physical address of each property where you have a paint job. this would be a one-to-many child table of tblCustomers: one customer may have many properties, and each property belongs to one customer. based on the way your tables are currently set up, i'd say tblJobs would be a child of tblProperties, rather than of tblCustomers.

a few more comments:

i see that you have a Notes field in tblCustomers. no problem. suggest you consider also adding a Notes field to tblJobs - or to tblJobs instead of tblCustomers. you'll have to discuss with your customer which is more appropriate - or perhaps both will be useful and on-target.

in tblJobs, field JobScope looks like a candidate for a related table with identified "scopes", and a foreign key into tblJobs. ditto for fields RoomFacade and Surface, in tblJobDetails.

in tblJobDetails, field PaintDetailsID does not belong. if i understand your diagram correctly, tblPaintDetails is a child of tblJobDetails; the foreign key belongs in the child table, not in the parent table.

in tblPaintBrands, field PaintDetailsID does not belong. again, don't "cross-link" parent and child tables - the link goes from parent to child, not vice versa. so the foreign key field (from the parent table) goes in the child table, and that's it. and looking closer, it looks like you've set up tblPaintBrands as a child of tblPaintDetails - that should be the other way around: one paint brand may be used for many paints, and each paint in a paint detail record is of one brand.

in tblJobDetails, you should not have both PaintColorID and PaintColor fields. if you have, or if you should have, a table listing paint colors (perhaps with a linking table to tblBrands?) then you would have the PaintColorID in tblJobDetails as a foreign key. if you do not have a tblPaintColors, then there would be no ID, so no need for a PaintColorID field - just a PaintColor field.

my only other question at this point is: might a single room (or exterior) have more than one facade and/or surface?

and a final note about "supporting" tables, such as the possibilities suggested thus far: categories (by Daniel) and job scope, room facade, and surface. generally, when the business process describes/defines a characteristic specifically, especially with words that are used by that business or industry, it's a good idea to put those terms in a table of their own, and link them into the primary data table(s) where appropriate. this setup ensures consistent data entry - and consistent data can be much more easily queried, and sliced 'n' diced for data analysis. and sooner or later, just about every business wants to analyze their data to help them make business decisions of all kinds.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
DanielPineault
post Feb 11 2019, 04:16 PM
Post#6


UtterAccess VIP
Posts: 6,404
Joined: 30-June 11



prefixing objects still holds value in the cases that they are all pooled together so you can easily differentiate them. For instance, when using custom groups, the Unassigned Objects just lumps everything together, having the prefixes can sometimes aid to quickly identify what is what.

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
chrmaz
post Feb 11 2019, 04:19 PM
Post#7



Posts: 6
Joined: 11-February 19



QUOTE
Normally, you woud break off fields such as Phone or Email into separate tables to enable multiple entries

tblPhones
CustomerId
PhoneTypeId -> FK to a related table with values like Home, Business, Cell, ...
Phone
PhoneExtension

tblEmails
CustomerId
EmailTypeId -> FK to a related table with values like Personal, Business, ...
Email

You might even wish to break the Address into a separate table as you might deal with people with multiple properties, no?

Category should be FK to a related table with the values.

Stay away from attachments! See http://www.devhut.net/2017/04/20/access-be...shooting-steps/ and http://www.devhut.net/2016/10/03/adding-at...ccess-database/


Ok, i get breaking out the phone, email, and category fields into separate tables (category was a lookup field combo box filled from a value list and i see from one of your links that i should stay away from that). i'll go with linking to files instead of attachments, not sure they are going to use that anyway.

As for the multiple addresses, the way i see it, each person would only have one single contact address, but they might have jobs at multiple properties, so that would be table related to tblJob, but the painters don't (currently, at least) have a need for job addresses in this DB.

One more question if you could. i followed an Access tutorial online (http://holowczak.com/microsoft-access-2007-and-2010-tutorial/ - it is a sample bank DB) and one of the things they did was to make the CustomerID field in the Accounts table a lookup query sourced from the Customer table. i was going to do the same thing in my DB and have the CustomerID field in the Job table be a lookup field, but, if i am understanding your first link (he says, "Never use the Multivalued Fields (Lookup Wizard) data type (instead create proper parent/child tables and make your own!)", i should not do this? i'm not sure i'm always understanding the lingo correctly.

Thanks again! Big help.
Go to the top of the page
 
Mickjav
post Feb 11 2019, 04:21 PM
Post#8



Posts: 32
Joined: 25-November 18



Try Looking at a few of the Template databases from microsoft there's a time and billing And Time card Db They might give you a few idears
Go to the top of the page
 
chrmaz
post Feb 11 2019, 05:51 PM
Post#9



Posts: 6
Joined: 11-February 19



Thanks Mickjav, my DB is sort of based on MS contacts template, but it seems like MS
templates do not follow best practices
Go to the top of the page
 
projecttoday
post Feb 11 2019, 09:04 PM
Post#10


UtterAccess VIP
Posts: 10,605
Joined: 10-February 04
From: South Charleston, WV


A "unique identifier" used in another table is called a "foreign key". I realize that this terminology tweak is not a big deal, but you should be aware of this.

If the relationship between JobDetails and PaintDetails is one-to-many, you don't want paintdetailsid in jobdetails. jobdetailsid in paintdetails is sufficient. You do need a paint colors table.



--------------------
Robert Crouser
Go to the top of the page
 
chrmaz
post Feb 13 2019, 05:12 PM
Post#11



Posts: 6
Joined: 11-February 19



Hi everyone.Thank you all so much for the responses and help.

@projecttoday the online DB modeler i used has unique identifier as a field type. There is also, if you look close, the FK to represent that it is the foreign key. Thanks for that and for spying the the double relationship.

@tina t (and anyone else for that matter) Thanks for all of your suggestions and pointers. To answer one of your questions a room or facade might indeed have multiple surfaces to paint (eg, wall, trim, wall board, molding...). i'm not sure that i'm understanding your final comment about putting the jargon into it's own table.

One point of question: Job scope has three possibilities Interior, Exterior, Both Interior and Exterior. i originally had it in tblJob as a combo box with just those values. Is that so bad? Why should it be it's own table?
Also (ok, two points): i was going to have the CustomerID field in tblJob be a lookup query selecting ID, FirstName, and LastName from tblCustomer. Would that be bad?

i've attached the re-worked DB model and a sample of Job data for reference.

Thanks for all the help you've all given already!

Attached File  DBSchema2.png ( 46.62K )Number of downloads: 3
Attached File  DB_sample_data.png ( 35.27K )Number of downloads: 0
Go to the top of the page
 
projecttoday
post Feb 14 2019, 10:13 AM
Post#12


UtterAccess VIP
Posts: 10,605
Joined: 10-February 04
From: South Charleston, WV


Okay, I see the FK now. You've still got paintcolor and paintcolorID in the same table.

--------------------
Robert Crouser
Go to the top of the page
 
chrmaz
post Feb 14 2019, 11:40 AM
Post#13



Posts: 6
Joined: 11-February 19



projecttoday thanks for the eyes on that, but i think maybe it is my mistake in naming that field. That represents vendors id# for that particular color mix. It is not a key. The color field is only an approximate (they might just put red for any variant of red), but the id# is what the vendors use to make a specific color hue.
Go to the top of the page
 
projecttoday
post Feb 14 2019, 11:44 AM
Post#14


UtterAccess VIP
Posts: 10,605
Joined: 10-February 04
From: South Charleston, WV


Okay. Change the name.

--------------------
Robert Crouser
Go to the top of the page
 
tina t
post Feb 14 2019, 05:17 PM
Post#15



Posts: 5,746
Joined: 11-November 10
From: SoCal, USA


QUOTE
and a final note about "supporting" tables, such as the possibilities suggested thus far: categories (by Daniel) and job scope, room facade, and surface. generally, when the business process describes/defines a characteristic specifically, especially with words that are used by that business or industry, it's a good idea to put those terms in a table of their own, and link them into the primary data table(s) where appropriate. this setup ensures consistent data entry - and consistent data can be much more easily queried, and sliced 'n' diced for data analysis. and sooner or later, just about every business wants to analyze their data to help them make business decisions of all kinds.

QUOTE
@tina t... To answer one of your questions a room or facade might indeed have multiple surfaces to paint (eg, wall, trim, wall board, molding...). i'm not sure that i'm understanding your final comment about putting the jargon into it's own table.

if you use an open Text field to store characteristics that are "jargon", you're going to get misspellings and abbreviations, etc, that will make the data much less useful for analysis, unless somebody spends time and effort to "clean it up" before analyzing it.

by putting these defined terms into a table, and then putting that table's primary key field into your data table as a foreign key field, you can present the user with a combobox or listbox control to limit the data entry to a term that's in the "supporting" table. this makes data entry easier/faster/more accurate, and it makes it much much easier to analyze the data later.

QUOTE
One point of question: Job scope has three possibilities Interior, Exterior, Both Interior and Exterior. i originally had it in tblJob as a combo box with just those values. Is that so bad? Why should it be it's own table?

well, first of all, putting a combobox (called a Lookup field) in a table is a really bad idea. it causes lots of problems that are better avoided. and there's no pain associated with the avoidance, because data should not be entered directly in tables anyway, so the combobox is not useful there. data entry should be done in forms, and combobox (and listbox) controls in forms present no problem at all and are in fact very useful.

in addition, i tend to stay well away from using value lists as the RowSource of any combobox/listbox, unless i can be reasonably sure that the list will never be updated (and i very rarely say never or always, when it comes to databases). a table using a surrogate primary key is much easier to maintain - update the table once as needed, instead of going to every place that a combobox/listbox uses the value list and updating it manually. it's especially a blessing if you find that you misspelled a value, or if the term changes while its' meaning doesn't. in either case, you can update a table once and you're done. fixing a value list again means going to every object that uses those values in a combobox/listbox AND updating the data in the main data table with the corrected value(s) - a more involved and delicate task, by far.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd February 2019 - 09:17 AM