Programming Forums > Web Development and Design > Other Web Development Languages

k4pil · Nov 11th, 2005, 4:55 AM

HI ppl!

I have the schemas below.

Any help with normalisation??

Schemas

User (userID, fName, sName, access, department)

Password( userID, password)

Customer (customerID, fName, sName, address)

Product (productID, prodDesc, prodName, cost, supplier)

User (userID, fName, sName, access, department)

Do these make sense????

Thanks in advance

DaWei · Nov 11th, 2005, 6:36 AM

We don't know what your ultimate goal is, or how that data's related. If you're knew to this sort of thing, you might want to check out entity-relationship diagrams. For sure, I wouldn't have "User" there twice.

k4pil · Nov 11th, 2005, 6:44 AM

I've mapped these using entity relationship diagrams. It is possible to normalise data without entity relationship diagrams.

I have normalised the data, but just wanted to check. User shouldnt be there twice.

If anyone can look at these schemas and check whether the data is normalised, id be very grateful.

DaWei · Nov 11th, 2005, 6:47 AM

Quote:

It is possible to normalise data without entity relationship diagrams.

No chit, Dick Tracy.

k4pil · Nov 11th, 2005, 6:50 AM

Hopefully this puts the schemas into context.

Schemas

User (userID, fName, sName, access, department, userID, password)

Customer (customerID, fName, sName, address)

Product (productID, prodDesc, prodName, cost, supplier)

Well, the user and the customer are separate. I am developing an online business appliccation.

A user is a member of staff for example. He has a department, password, userID etc.

One user must create 1..* customers, while a customer must be created by a user.

The customer also has customerID, fName etc.

The customer then must purchase n products.

The products have a unique product ID, product description etc. A product may be purchased by 1..* customers.

Thanks in advance

xavier · Nov 11th, 2005, 6:57 AM

User (userID, fName, sName, access, department, userID, password)
That's not good .. your copy paste was wrong
The addres in customer should be devided in more fields like state, city, street ... etc
Should have a table : Purchased (id_purchase, productID, customerID, quantity)
Suplier(id_suplier, suplier_name) should be another name
Product (productID, prodDesc, prodName, cost, id_suplier)
Also : Department( department_id , department_name)
and : User (userID, fName, sName, access, department_id )

that's all for now , i think

DaWei · Nov 11th, 2005, 7:03 AM

Quote:

Originally Posted by about.com

First normal form (1NF) sets the very basic rules for an organized database:

* Eliminate duplicative columns from the same table.
* Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second normal form (2NF) further addresses the concept of removing duplicative data:

* Meet all the requirements of the first normal form.
* Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
* Create relationships between these new tables and their predecessors through the use of foreign keys.

Third normal form (3NF) goes one large step further:

* Meet all the requirements of the second normal form.
* Remove columns that are not dependent upon the primary key.

I'm really not "getting" the point of your post. None of this guarantees that the application is any good, merely that issues of redundancy, et.al, have been addressed. It's an exceedingly simple schema. There isn't any mention of things like tracking a customers purchases, relating them to a salesperson (user?), etc. It looks somewhat too skimpy to be truly useful, and doesn't meet needs that you're hinting at (customer must be created by user, users must create customers).

k4pil · Nov 11th, 2005, 7:04 AM

thanks Xavier, thats really helpful, and my cut and paste did go wrong.

Il give it another go and show my results.

Thanks again

k4pil · Nov 11th, 2005, 7:06 AM

I am new to this.
So help me out?

k4pil · Nov 11th, 2005, 7:27 AM

Is this any better??

User (userID, fName, sName, access, department)

Customer (customerID, fName, sName, address)

Product (productID, prodDesc, prodName, cost, supplierID)

CustomerAddress (CustomerID, addressLn1, addressLn2, addressLn3, city, county, postcode)

Purchase (purchaseID, productID, customerID)
productID and customerID are foreign keys.

Supplier (supplierID, supName)

Nov 11th, 2005, 4:55 AM	#1
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	help with normalisation of schemas HI ppl! I have the schemas below. Any help with normalisation?? Schemas User (userID, fName, sName, access, department) Password( userID, password) Customer (customerID, fName, sName, address) Product (productID, prodDesc, prodName, cost, supplier) User (userID, fName, sName, access, department) Do these make sense???? Thanks in advance

Nov 11th, 2005, 6:36 AM	#2
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	We don't know what your ultimate goal is, or how that data's related. If you're knew to this sort of thing, you might want to check out entity-relationship diagrams. For sure, I wouldn't have "User" there twice. __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Nov 11th, 2005, 6:57 AM	#6
xavier Professional Programmer Join Date: Oct 2004 Location: .ro Posts: 383 Rep Power: 5	User (userID, fName, sName, access, department, userID, password) That's not good .. your copy paste was wrong The addres in customer should be devided in more fields like state, city, street ... etc Should have a table : Purchased (id_purchase, productID, customerID, quantity) Suplier(id_suplier, suplier_name) should be another name Product (productID, prodDesc, prodName, cost, id_suplier) Also : Department( department_id , department_name) and : User (userID, fName, sName, access, department_id ) that's all for now , i think __________________ Don't take life too seriously, it's not permanent !

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Nov 11th, 2005, 6:44 AM	#3
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	I've mapped these using entity relationship diagrams. It is possible to normalise data without entity relationship diagrams. I have normalised the data, but just wanted to check. User shouldnt be there twice. If anyone can look at these schemas and check whether the data is normalised, id be very grateful.

Nov 11th, 2005, 6:50 AM	#5
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	Hopefully this puts the schemas into context. Schemas User (userID, fName, sName, access, department, userID, password) Customer (customerID, fName, sName, address) Product (productID, prodDesc, prodName, cost, supplier) Well, the user and the customer are separate. I am developing an online business appliccation. A user is a member of staff for example. He has a department, password, userID etc. One user must create 1..* customers, while a customer must be created by a user. The customer also has customerID, fName etc. The customer then must purchase n products. The products have a unique product ID, product description etc. A product may be purchased by 1..* customers. Thanks in advance

Nov 11th, 2005, 7:04 AM	#8
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	thanks Xavier, thats really helpful, and my cut and paste did go wrong. Il give it another go and show my results. Thanks again

Nov 11th, 2005, 7:06 AM	#9
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	I am new to this. So help me out?

Nov 11th, 2005, 7:27 AM	#10
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	Is this any better?? User (userID, fName, sName, access, department) Customer (customerID, fName, sName, address) Product (productID, prodDesc, prodName, cost, supplierID) CustomerAddress (CustomerID, addressLn1, addressLn2, addressLn3, city, county, postcode) Purchase (purchaseID, productID, customerID) productID and customerID are foreign keys. Supplier (supplierID, supName)

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Web Development and Design > Other Web Development Languages

help with normalisation of schemas