Why Be Normal? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Why Be Normal?

Author

Topic

MrKicks
Starting Member

6 Posts

Posted - 2004-10-30 : 04:52:37

Is there a point where you can be too normal?

I am working on an Ecommerce Project where the ingredients of the products are to be displayed. Each Product may have up to 30 ingredients and each ingredient has on Medicinal/healing atribute.

After reading several articles about normalization I feel that I should do my best to "Be Normal" but in my starting to design the DB it looks as if this is going to add a tremendous amount of work to not only enter data initially but in my queries and updating.

And if I were to Normalize is this how I would do it?

Table - Products
1 prod1 Price
2 Prod2 Price
3 Prod3 Price

Table - ProdToIngredients
Column 1 is Product reference, Column 2 is Ingredient reference
1 1
1 2
1 4
2 1
2 2
2 3

Table - Ingredients
1 Ingred1 Atribute
2 Ingred2 Atribute
3 Ingred3 Atribute
4 Ingred4 Atribute

Thank You,
MrKicks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-30 : 07:17:51

yes. that's a classic many to many relationship. perfectly ok.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-30 : 07:29:42

You can set up a Stored Procedure that receives all the data from the user's data entry form and stores it into the [multiple] appropriate tables - if that helps!

Kristen

MrKicks
Starting Member

6 Posts

Posted - 2004-10-30 : 23:29:07

Thank you both. It just seems like a lot of work to set this up and once setup I will need complicated (to me anyway!) queries to gather all the data from several tables just to put it back to a single row of usable data. I guess I am just missing the benefits.

When I want to pull the data back out am I using a stored procedure as well?

My Product table will be similiar to the following:
Ref(n) = Reference to a reference table that will reference another table that I want data from. (if that makes any sense)

ID Name Description Ref1 Ref2 Ref3 Ref4 Ref5 Price

If there was only one Referenced Column I can get the data with a join, however, when I add the second reference column I am lost. Is it still a join I will be doing? All the data in all the tables will be based on the 'ID' column.

Any pointers to good books, articles or tutorials dedicated to Querying Normalized data would be much appreciated.

Thanx again,
MrKicks

Kristen
Test

22859 Posts

Posted - 2004-10-31 : 01:47:06

"When I want to pull the data back out am I using a stored procedure as well"

Probably preferable for three main reasons:

1) Users have permission for the SProc, and not for the table
2) Performance should be faster as SQL Server will cache the query plan
3) The "business rules" behind pulling back the data can be adjusted in the SProc in the future (this includes WHERE you get the data from - so you can change the structure of your database without needing to change the aplpication- the SProc will still return X, Y and Z ...

You could create a VIEW that has all the JOINs in place, so that you don't have to remember/type them each time.

SELECT P.ProductRef, P.ProductCode, P.ProductDescrtption,
I.IngredientCode, I.IngredientAttribute
FROM Products P
JOIN ProdToIngredients PI
ON PI.ProductRef = P.ProductRef
JOIN Ingredients I
ON I.IngredientsRef = PI.IngredientsRef

(Put "CREATE VIEW MyViewName AS" in front of that SELECT statement if you want to create a view)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources