table normalisation problems - help!!

[Beetle192]

hi

Can anyone help? I need to normalise my tables, and ive read articles and tutorials on how to normalise, i think i understand it but i dont like the look of my tables, below.

All employees are contained in the employee table.

Employees (Serial, Firstname, lastname, address, street, city, county, postcode, telephone, useremail, odb)

When employees want to use this application im designing, HR put them in a separate table, below.

HR (serial, useremail)

Once the user enters the rest of his/her details they get saved in the Users table below.

Users (serial, contractname, location, dept, managername, odb, useremail)

so once the users have registered to use the application, the registration details are checked against the following table.

Password (serial, useremail, password)

products are categorised into 2 types, they are either in a catalogue or its a contract, not much difference really and am thinking of putting them 2 togther into 1 table, wot do u think?

Catalogue_items (catalogueID, partID, itemdesc, price)

Contract_items (contractID, partID, itemdesc, price)

Each catalogue and contract has a specific commodity code for accounting purposes!

CommodityCode (commodityname, catalogueID/contractID)

Once an order is submitted in the application, the manager has to approve the order request, manager details below.

Manager (serial, managerfname, managerlname, useremail, odb)

The dept details below. dept no = odb!

Dept (odb, deptname, location)

once an order has been submitted, all the details from the order should get saved into a separate table, so can query and search this table later.

Requisition (ReqID, catalogueID/ContractID, Commodityname, PartID, itemdesc, price, quantity, noofitems, totalprice, datesubmitted, useremailofraiser, serialno, raisername, Requestorcode, requestorfname, requestorlname, odb, address, street, city, county, postcode, telephone, requestoremail, managerfname, managerlname, manageruseremail, fpmanagerapproved(Y/N), odbmanagerapproved(Y/N), rejected details)

not sure of the primary keys!

raiser = the person who is raising the req(order) on behalf of the requestor.
order should get approved by both dept manager(odbmanager) and finanace manager(fpmanager)

i know its long but can anyone help me? am in desperate need of help!

thanks
a stressed beetle!

 

[SemperFiDownUnda]

first thing I notice is you can combine your HR and Password table because the password table should have everyone in the HR table (minus the lag of registering) But during that time just have their password null and don't let someone with a null password to log in.

Combining the Catalogue_items and Contract_Items into one table ... hmmmm ... my first thought is to leave them seperate if you don't need to search across both of them at the same time. Otherwise you'll either need to be able to tell that they are a catalogue item or a contract item by the primary key or add column indicating which they are.

I take it multiple Catalogue/Contract Items can share the same CommodityCode? If so that looks ok otherwise move it to the parent table.

Manager should not need their email in their table since it currently lives in 2 other tables.

Requisition
1)Commodityname - this isn't needed as you can derive it from the item. looking at it now you should combine Contract/Catalogue item tables.
2)itemdesc - you can grab this from the Contract/Catalogue table
3)price - if they are allowed to override the base item price leave it otherwise take it out. But make sure you build in history capability to track price changes in the Contract/Catalogue table.
4)quantity, noofitems - what is the difference between these?
5)useremailofraiser, raisername, Requestorcode, requestorfname, requestorlname, odb, address, street, city, county, postcode, telephone, requestoremail, managerfname, managerlname, manageruseremail - all this information should be held in your employee table, if I understand your model right. If these people are not employees but any job blow maybe you should introduce some storage of there data and when they use the system agian another time they wouldn't have to enter this info but seeing you have info like manager then I'm assuming the first.