Over Normalization! (1 Viewer)

[RickDB]

Is this something that ever comes up?

I set out to build a database, and one of the first things I hear/read over and over and over again is normalize your table structure.

This seems to make many simple things a complete pain in the arse.

Say I have this basic Customer table:

tblCustomers
CustomerID
DateEntered
CompanyName
FirstName
LastName
EmailAddress
---MARKER---
PhoneHome
PhoneWork
PhoneCell
Address1ShipTo
Address2ShipTo
CityShipTo
StateShipTo
ZIPShipTo
Address1BillTo
Address2BillTo
CityBillTo
StateBillTo
ZIPBillTo

Rules of normalization would dictate that I should take Phone & Address Information out and place them into their own seperate tables. Great. Love it. Makes perfect sense.

Now, when it comes to making a form for data entry, all of the fields above ---MARKER--- can easily be made into a Form. BUT, now when it comes to the normalized data entry, Access leaves me with crappy Subforms to mess with. Datasheet subforms are horrible for this type of data entry. Horrible.

With Datasheet Subforms, I can't organize data to display effectively in a 800pxX600px enironment. And data entry relies on familiarity with the system and the tab key.

I obviously am struggling with the concept of setting up forms, but NOTHING I read (and I have been reading a ton lately on this) hits on this problem with answers and samples that look like what I am trying to build.

The answer always seems to be to use a Subform, or make a seperate Form all together. When team members look at a form, I would like all Customer information to be presented on one screen in a comprehensive manner, but this is eluding me big time. Be it stupidity or ignorance, I've no idea, but I generally have good luck in grasping general concepts when it comes to computers. This, however, is proving impossible. And I refuse to relegate entry of critical data to an indecipherable datasheet-view subform.

If anyone can offer insight into my woes related with a presenting normalized data model for easy data entry, I would appreciate it.

 

[KenHigg]

Rick, At first glance I think I would do something like the following:

tblCompany
Record_Num PK
Name
etc

tblCustomer
Record_Num PK
Company_ID FK
Name
Phone
etc

tblAddress
Record_Num PK
Customer_ID FK
Address_Type (Ship_To, Bill_To, etc)
Address_1
Address_2
City
etc

 

[Banana]

You *don't* have to use subforms.

Instead, use a query that pulls together the tables, use it as recordsource for your form. That will give you all controls without the subforms.

Subform is only necessary if you cannot create a updatable queries.

A query is updatable if Access/Jet can understand how it relates; such as this

tblCustomers -> tblOrders -> tblPrdoucts.

For address and contact tables, since they are related to customers, you can just join them to customers and work from there.

but not if you have something like this

tblCustomers -> tblOrders and tblCustomers -> tblAddress

 

[wazz]

and...a subform doesn't have to be in datasheet view; it can be in normal form view (single form).
- create the subform separately, as a normal-looking form; make it appear just the way you want for data entry. close it.
- open the form that will be your main form.
- when you're ready, *drag* the first form (do not open it) from the forms-window onto the open form and you have your subform.

 

[Len Boorman]

And back to the Normalisation question

What happens when one of your customers has 3 Ship To Addresses or 4 or 6

You have to change your table and your interface

and then they have 15 alternative Billing addresses.

And then you boss says

Please give me a list of all Customers and Ship To Address that are in the xxx are

Which Ship to Address field do you search cos there may be 1,2,3 xxx

Normalised there is 1 basically even if Joe Smith has 27 different Ship To Addresses


Okay so maybe thy will not but one of the purposes of normalisation is to ensure that you can handle this sort of situation without having to rewrite your application to some degree.

Len

 

[RickDB]

Thanks for all of the replies!

I was venting when I wrote this (I had about 2-3 days where I only had a bit of time here and there and was unable to make any progress), but have since started to understand this more and more.

I am trying to understand all I can about subforms, and queries as well. I should have plenty of time to dedicate to this today and I hope to post something of worth to fish for pointers and feedback.

Thanks for the tip on subforms Wazz, I am going to attempt that method to allow 2 instances of address entry on the main form (Ship To, Bill To) in conjunction with a button for additional addresses (in case of what you suggested Len).

Thanks for the pointer on queries too B, I understood that I can build forms on top of queries, but to be honest, have worried about data integrity in that case.... And thanks for the Data Model suggestion Ken, I have posted my model elsewhere on the forum, but I see a slight adjustment I should make based on your input.

Thanks all! Here's to hoping executing my plans goes as smoothly as possible...

 

[Pat Hartman]

Common sense is allowed to be applied when designing a database. ShipTo and BillTo are not a repeating group and so it is not necessary to relegate them to a many-side table. Many organizations choose to do so for the sake of storage space. If most of the time, ShipTo and BillTo will be the same address, it takes less storage space to store a foreign key than an entire address. Similar thinking applies to the contact method (phone, fax, email, etc). It is certainly more flexible to create a many-side table for this because people are accumulating many ways of contacting them and who knows what will happen tomorrow. You'd certainly rather add a contact Type to a lookup table than modify your table and form design.

Typically BillTo is contained in the customer record but ShipTo is contained in the Order. If you need to maintain multiple ShipTo addresses for each customer then a separate table is required. If most of the time the BillTo and ShipTo will be the same, then a separate table for ShipTo addresses only will probably save you space.

Subforms that show only a single record can be made to be seamless on the main form. No one would know by looking at the form that they were interacting with a subform. Continuous forms or Datasheets are hard to hide but you can still make them look nice.

 

[RickDB]

Thanks for the insight. I have been working with subforms a lot this past couple days and have found all of the controls that are required to make it seamless into a form.

This makes design MUCH easier (I really thought a subform might have to be a datasheet for a while, like I said before... Nigel from Spinal Tap).

I'm pretty comfortable with the concept now, but I'' findo out if deisgning for the data model I used will lead to problems basd on my overuse of subforms. But thats one thing queries are for also... We'll see what happens.

Thanks.

 

[ions]

Banana said this above.

You *don't* have to use subforms.

Instead, use a query that pulls together the tables, use it as recordsource for your form. That will give you all controls without the subforms.

Subform is only necessary if you cannot create a updatable queries.

A query is updatable if Access/Jet can understand how it relates; such as this

tblCustomers -> tblOrders -> tblPrdoucts.

I don't understand this Updatable Query. I thought in order for access to create the foreign keys in the tblOrders and tblProducts for you automatically there has to be a master child link on the form otherwise you would have to insert the foreign keys in yourself programatically no?

 

[Pat Hartman]

Access will do it for you in a query also. Create a query that joins two tables with a 1-m relationship. Enter values in the 1 side fields and then enter something in the m-side fields and see what happens. This may only work if a relationship has been defined in the relationship window.