Creating friendly forms from normalized tables

[robsworld78]

Hi, I've created a couple databases which are working good for now, we'll see what the future brings for them.

I'm creating more and have been reading alot about design and normalization, and want to learn how to do them properly. I thought I was doing it correct but turns out I fail form1 of normalization, not good. I've figured out how normalization works and I'm sure I can do it but now I fail to see how I make it work with "user friendly" forms.

Here's what I fail to see.

Example

My customer table has "address1" and "address2" and yeah I always said to myself what if they have 3rd or 4th address. Ok so I normalize this table and move the address to a new table which links to the customer so now they can have unlimited address and the tables work better, I get that.

But how to I create a form without subforms to enter the data in the tables. In my un-normalized tables I simply add all the fields to the form and users can enter in "address1" and "address2" but if its normalized how can they enter 2 addresses in the form without a subform for addresses. If there isn't a subform they would have to use another form for addresses.

Then if we normalized phone numbers we need another subform, etc...

I just can't see this working?

I've been looking hard for examples but can't find any, all microsofts templates don't see to follow normalization or they leave out things that would require it. Plus I hate all the user forms the templates use, I can't imagine having to use forms like that to keep things normalized?

So how do you deal with it? Any comments or links to reading would be great, I just can't find the info I'm looking for due to not having the correct keywords for searching I suppose.



Any help would be appreciated, thanks!

[Dal Jeanis]

One of the things to remember about normalization is that it depends on usage. "Address1" and "Address2", in the US, are often two different lines that are part of a single physical address. The first might be the number and street, the second either null or an apartment or suite number. In which case, they are related field that belong on the same record, and are in normal form.

The other situation where keeping two addresses on a single record would be in normal form is if every customer was expected (or defined by the system) to have one billing address and one delivery address, which might be the same. As long as that's the usual case, and the number of addresses isn't expected to proliferate the way telephones have, then you're still in normal form.

Another conceptual kink is that you don't necessarily have to have a sub-FORM to do entry into, or display from, a different table. For example, you could have a listbox that lists all the phone numbers that are currently linked to the main record. You get the data into the listbox by setting the RecordSource property of the box, and the SQl in the Recordsource uses one of the other bound field to select the data. Above that listbox, you could have a text box for entering the phone number, with a dropdown box for what KIND of phone number it was, and a button to click to add the entered phone to the box. All on the main form.

Of course, the phone numbers couldn't be added to the other table until the required key field had been generated for the new record, so you have to understand the order that things occur, when you are designing your forms.

Does this discussion help at all?

[ssanfu]

While it is good to strive to be at least the 3NF, there is times when over normalizing can cause problems.
So, in the truest sense, the address fields and phone numbers should be in separate tables, but it really doesn't hurt (too much) if they are in the same table.

Unless you change "address" to e-mail. Some people have more than one e-mail address (I have around 8). Same for phone numbers. It is easy to have 4 or 5 numbers: home, work, cell, contact, fax (yes, some people still have fax machines). Then you might think about having tables for e-mail addresses and phone numbers.

Sometimes it makes sense to have a slightly de-normalized table. In my databases, I have two fields for addresses. Works good.
If I had the possibility of business (physical) address, mailing address and shipping address(s), I would use a separate table.

If you have a separate table for, say, addresses, you don't have to use a subform. It could be a pop-up form that allows you to add addresses. Whatever works best for your users and/or you.

My $0.02 worth

[robsworld78]

Thanks Dal and Steve, that helps, those are a good alternatives you both mention. Dal your suggestion for phone numbers made me think of Maximizer, it was a complex contact database and that's how they handled phone numbers, well close to it.

The popup window is a good idea and would probably work for a lot of scenarios.

Anyone else have alternative suggestions to subforms?

[Missinglinq]

...While it is good to strive to be at least the 3NF, there is times when over normalizing can cause problems...

Amen to that! Spending 8-10 hours a day on this and other forums (I'm in forced retirement) I see this kind of thing all the time! Normalization is a very necessary component of a well designed Database, but it can make tremendous amounts of extra work when carried too far! If your client has homes in Palm Beach, the Bahamas and the South of France, do you really need phone numbers for all of these locations, or is the client apt to have call forwarding? If the client has 7 nicknames, do you really need to know all of them in order to sound friendly, when you speak to him?

Recently saw a question, from a self-proclaimed 'professor of programming,' wanting to know how to easily make a single form (no Subforms, please)using separate Tables for a client's

HouseNumber
StreetName
CityName
StateName
ZipCode
AreaOfCountry
NameOfCountry

Seven Tables for storing a single address! I'm sure someone will disagree with this statement, but to me that's just plain silly!

The really scary part, to me, is that this poster is teaching this approach to others!

Linq ;0)>