Normalization?

[Frank Pytel]

Hello;

Hoping someone can help with a design issue. I have to learn C# for work.
Part of my work will be calling information from a database. Not sure of the
type. I thought that it would be good practice to design an application and
integrate it with a Jet database. I understand that much of the work will
allow me to migrate to SQL or mySQL if needed.

So, I am building a grocery shopping list as an example. My question is
regarding how to create the tables.

I have categories of FrozenFruit, FreshFruit and CannedFruit. I realize that
I will be duplicating some of the input (Peaches) but I will need the lists
within C# for auto populating or suggestive typing in form txtFields.

This is where it gets tricky for me. Should I go with a Brand
table(Cambell's, Progresso) listing Soup (Clam Chowder, Chicken Noodle) with
a size and price in columns 3 and 4? This will leave out an auto populating
Soup list because size and price may differ by brand (10.25 oz Chicken Noodle
vs. 10.75 oz Chicken Noodle)?

I am a bit perplexed and any advice you can provide would be greatly
appreciated. My skill level with database design is below novice. Idiot would
be a better descriptive term. I am basically typing my tables into Excel and
importing them so that I can have the wizard automatically fill in the
NoDuplicates and Key assignment.

Thank you in advance for any help. All responses and opinions will be
greatly appreciated and helpful.

God Bless

Frank Pytel

 

[adam brett]

Dear Frank,

You have a long way to go. I don't wish to patronize, but DB design is
really a complex set-based mathematical process & needs to be taken pretty
seriously. It is almost never worth cutting corners as in the end you have
to re-engineer in the additional features which is always more painful than
putting them in at the start.

There are plenty of links on the web on "designing databases", and some very
decent books out there.

Here are a few notes:

--

Firstly think about 3 types of data in a db table:

1. Meta data:

- ID
- AuthorEditing
- DateEdited

.... i.e. some of the fields in the table contain data about the process of
controlling the data in the table. It is a bit recursive, but once you get
you head around it is very powerful. I _always_ put an ID field on every
table and get the database to set it to a unique number for every record in
every table. In this way you can easily locate any record just from its ID.
Fields like "DateEdited" are incredibly useful as they allow you to return
records that were edited "last week" etc.


2. Data:

-ProductName
-ProductPrice
-Description

.... these are "real" data that the user actually sees and wants to know
about. Most of the fields you are currently creating are probably of this
type.

3. Relationship data:

- ProductTypeID
- ProductCategoryID

.... these are fields in the table (usually holding simple number value)
which allow that table to read data from other tables. Note that this
powerful feature is a core of all Relational Database design, and requires
the ID fields I mentioned in 1. above. Once you have these fields you can
create JOINs linking tables so that your resulting data makes sense but
contains data pulled from multiple sources.

Imagine the following Product:

"Happy Meal."

It consists of Products:

"Fries",
"Burger"
"Soft Drink"
"Box"
"Free Toy"

Note that "Soft Drink" is one of 4 possible drinks, and that Free Toy change
every month depending on the particular Film being promoted, so a database
managing this data would have to be able to manage those variations too.

Happy Meal has a price, a name, a description and some other features. Then
it consists of links to the 5 related products. In a manufacturing situation
"Fries" might include a sub-product "Potatoes" and "Oil" ... These links
would be held in a "Relationship table"

There would also be a table "ProductCategories" which might contain records
like:

Drink
Dessert
Main Course
Combination Meals

.... All of the above is just the tip of the ice-berg, but I hope gives you a
few ideas!

Adam

 

[Tom van Stiphout]

On Wed, 17 Dec 2008 02:58:15 -0800, Frank Pytel

You say you have to learn C#, not that you need to learn about
designing databases. If it's indeed only the former, stick with a
sample database like Northwind or (more challenging) the
AdventureWorks database, and focus on acquiring those language skills
first.
Find a C# newsgroup to help you when you get stuck.

-Tom.
Microsoft Access MVP

 

[boblarson]

Perhaps you should read this
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf