normalisation newbie

[PF]

Suppose you normalized a customer table and split it into 3 tables with the
access wizard.
Then you remove the lookup fields of the customer table because they are
'evil stuff' (...)
The customer table is link with the other with autonumber link fields

1. tblcustomer with address and all data
2. tblBuyer a list of 5 names
3. tblType a list of number

Question 1
What is the common use technique to add record?
Lets say the user add a customer, he enter a new customer number and data,
but when he will arrive a the buyer field what is the common way to handle
that on a form.
In vb if you want to add a record how do you do that?

Question 2
How come there are no question on normalisation in table design forum?
Is someone using that?

Question 3
When i import from text file i would like to see an example of text transfer
involving
data normalised with vb. Any link? I look in google without much result

Regards,
Pierre

 

[Tim Ferguson]

1. tblcustomer with address and all data
2. tblBuyer a list of 5 names
3. tblType a list of number

You have not given much of a clue about what these entities are, but I am

Question 1
What is the common use technique to add record?

Just go to the "new" record on the form.

Lets say the user add a customer, he enter a new customer number and
data, but when he will arrive a the buyer field what is the common way
to handle that on a form

Depends: you could use a combo box on the form with the RowSource getting
information from the Buyers table. Or a commmand button could launch a
dialog (another form) that would carry out a search of the Buyers table. Or
you could have a textbox where you enter the buyer's name, and then have
some code look it up and return the ID number.

Question 2
How come there are no question on normalisation in table design forum?
Is someone using that?

Huh? -- practically all of the questions that get answered here are about
relational theory and good schema design. Normalisation is one method to
get there.

Question 3
When i import from text file i would like to see an example of text
transfer involving data normalised with vb.

Not sure what you mean. "Data" are not "normalised", a design is. As for
text file import, there are many many ways to do it, depending on the shape
and cleanliness of the original data. Need more details.

B Wishes


Tim F

 

[PF]

Tanks for your answers.

For question 1

In tblcustomer, each customer has a buyer associated with it.
instead of repeating the name of the buyer in each row, i want to put the
buyer name
in a separate table with a link field.

If i do that i will have to modified the program use to retrieve information
from the tables.

I am wondering if it worth it. Will i save lookup time enough to justify the
modification.

Suppose you have a customer table with address and a separate field for
city.
Is it a common practice to normalize that field? i.e. make a separate table
for city? It seems to me that it will bring complexity.

For question 2
does the wuh? mean -"Wake up everyone is using it" If i make a search on
normalisation on this newsgroup the term is not appearing at all - i was
surprise


Question 3
If i understand correctly i will have to import the ascii file in a
temporary table
and then rebuild each table in a normalise way, i.e. build a table with
unique buyer name and unique id and then link it with the main table etc...

pierre

 

[rpw]

Try doing the search with a different spelling: normalization.

You'll get plenty

 

[tina]

i think you're not quite getting the idea of normalization and "supporting"
(lookup) tables. one issue in normalization is that you don't put multiple
instances of a value in different fields in the same record. for instance,
if you need to connect a salesman to several cities that he covers, you
don't put multiple city fields in the same table, as City1, City2, City3,
City4, etc. instead, you would put the cities in a child table. the salesman
table would have a one-to-many relationship with salescities table. one
salesman record can be related to many salescity records BUT each salescity
record is only related to one salesman. that's one step in the normalization
process.
supporting tables are a different issue, not really related to
normalization. you generally use a supporting table to list data that you're
going to use to populate a droplist. this makes it easy to 1) control and
limit the data that may be entered in a field and 2) ensure that the display
values are correctly spelled, capitalized, etc.
to solve your addresses issue: create a separate table for cities, as
tblCities
CityID (primary key - data type as AutoNumber)
CityName
enter all the city names you need - or think you may need - for your various
addresses.
in your addresses table, set the data type for the City field as Long
Integer. that matches the autonumber data type for the primary key field of
tblCities. set a one-to-many relationship between CityID (one) in tblCities
and City (many) in tblAddresses.
in your Address data entry form, use a combo box to enter the City name.
base the combo box on tblCities. read up on combo boxes in Help for details.
also, you can use the Control Wizard in form design view to build the combo
box for you - makes it very easy.

hth

 

[Tim Ferguson]

I am trying not to pepper you with too many answers, especially as I am
starting with so little information.

In tblcustomer, each customer has a buyer associated with it.
instead of repeating the name of the buyer in each row, i want to put
the buyer name
in a separate table with a link field.

Yesbutt... in db design, we start off thinking of 'entities' -- and people
are a common entity. As soon as we see something about someone selling to
someone else, we start to think of entities like 'People" and 'Sales'...
What happens if one of your buyers wants to be a customer (for example --
as I say, I don't know anything about what you are actually modelling)?

If i do that i will have to modified the program use to retrieve
information from the tables.

Well yes: you always have to nail down the db design before
wasting^Wspending any time on the program round it.

I am wondering if it worth it. Will i save lookup time enough to
justify the modification.

It costs (time, money, frustration, etc) about ten times more to correct an
error in the design after the app is finished, than it does at the design
stage. I don't know that you have an error though: just an uncomfortable
feeling.

Suppose you have a customer table with address and a separate field
for city. Is it a common practice to normalize that field? i.e. make a
separate table for city? It seems to me that it will bring complexity.

Okay: rule one -- normalisation is about semantics. You might regard an
address a single piece of stuff that you have to print on an envelope. On
the other hand, you might be interested in the details of where people
live: how many customers in Arkansas; what is the Townsend score for these
people; which customers live in cities greater than 500,000 population;
etc. In the second case, the Cities themselves become an entity in their
own right and deserve a table, while in the first case it's just part of
the text. Semantics means that you have to understand exactly what all your
data _mean_ in the context of how you are using them and how you might want
to use them in the future. Once you have that, then Normal Forms etc fall
out like grilling cheese.

does the wuh? mean -"Wake up everyone is using it" If i make a search
on normalisation

Don't bother searching: just browse some threads. Even when the word is not
used, it's the basis of most of the conversations going on here.

Question 3
If i understand correctly i will have to import the ascii file in a
temporary table
and then rebuild each table in a normalise way, i.e. build a table
with unique buyer name and unique id and then link it with the main
table etc...

As I indicated above, it completely depends on the nature of the text file
you start with. At best, you could just link it as an external table; at
worst you can write a VBA routine to parse it line by line. Usually it is
somewhere in the middle: a common method is to use something like
TransferText to make a temporary table and massage that using some queries
before appending the new stuff into the real tables. But it's an enormous
question in its own right, and I am by no means an expert in it.

Best wishes



Tim F