Database SQL Normalisation help!

[RichardK]

Guys i need help to normalise the following attributes. I asked for help from my tutor but i really don't understand what to do, here is what he wrote to me:

"This is the list of attributes you start with normalisation. They are
basically the information you should have collected from on an invoice. The huge table is labelled as Purchase therefore there’s only one PK for this table.

This is Unnormailsed form. The first step is to identify repeated groups – those attributes with exactly the same value on each invoice.

The Order ID on different invoice will be different so it is not a part of repeated group. What about OrderItemName, Quantity? The way to help you identify them is to create a mock table “Purchase” and fill it up with some fake data."


Attributes:

PURCHASE

ORDER_ID PK
DATE
CUSTOMER_ID
FIRST_NAME
LAST_NAME
ADDRESS
POSTOCDE
PHONE
CREDIT_CARD_NO
CREDIT_CARD_TYPE
OrderItemCode
QuantityOrdered
STOCK_CODE
StockItemNAME
UnitPrice
SUPPLIER_ID
SUPPLIER_NAME

[nicc777]

Check this site: http://www.devshed.com/Server_Side/M...al1/page3.html

You should be able to figure it out for your self, as we don't really like to do home work assignments here

Cheers

[RichardK]

Thanks but i'm not asking you to do this for me it's just can you exaplain what i need to do that's all.

[nicc777]

OK, ok...

Imagine you have a table like this:

table_products:
---------------
product_code
product_description
product_unitprice
product_unit

table_orders:
-------------
order_nr
date
time
product_code
product_qty
client_name
client_surname
client_address

Now, normalisation has to do with optimisation ( that way I understood it better ). So, the goal is to make your DB more effective, faster and logical.

The first table may be optimized, but what you will typically check there is that 'product_unit' references a table that contains the various unit definitions. This will improve consitancy in your products table.

The 'table_orders' has a lot of issues. First of all, we will have multiple entries for each order with more then one item, so there should be no primary key ( PK ). Next, the following fields could be moved to a seperate table: 'date', 'time', 'client*'

In this new table, let's call it 'table_orderinfo', I will also combine the 'date' and 'time' fields, as this is more effective and uses slightly less memory. Lastly, I will reference client data from a seperate client table. The tables will now look like this:

table_orders:
-------------
order_nr
product_code
product_qty

table_orderinfo:
---------------
order_nr [ PK ]
datetime
client_id

table_clients:
-------------
client_id [ PK ]
client_name
client_surname
client_address
etc...

The net result:

* The new structure will use less memory ( physical disk space, RAM and index sizes )
* Layout is more logical, so it's easier to write the fron-end interface
* You have more flexibility and you can mine for a lot more data then you normally could.
* Referencing between tables ensure that information bindings is consistant. In the first example, you can't assume that the client info will look exactly the same on each entry, but by using references ( JOIN in SQL ), you reference an ID, so it's much more consistant.
* Overall there should be speed improvements, for example, the order tax calculation will be faster as the table physically contains a lot less information then it used to.
* 'table_orderinfo' is the glue that binds 'table_orders' and 'table_clients'.

All that remains now is to decide how your indexes will look. That will depend on the final look of the DB structure.

Finally, database designs are flexable and many times you will find that only half way through a project you see a certain configuration is just not optimised. It's not alway to say your initial planning was flawed, but what happens is midway through any project there tend to be new or revised requirements. This happens all the time, and many programmers are frustrated by this aspect of client/programmer interaction - but that's part of the whole business. Also remember that there is always more then one solution to a problem. Sometimes it's difficult to decide which is better, because programmers/DBA's sometimes see a single problem from different angles.

Hope that made some sense

Cheers

[RichardK]

THANKS VERY MUCH!!! I will have a go and see if i can do it

[Clapital]

Originally posted by nicc777 You should be able to figure it out for your self, as we don't really like to do home work assignments here

LMAO ROFL good one!

[greenman]

Just in case nicc777 isn't available next time, Database Journal also has an article on the topic, hidden deep in the archives. You can find it here