database design-2

pts.
Tags:
Development
J2EE
Java
JavaScript
JSP
SQL
XML
sir, i am developing the order-process mgt (3-tier intranet app)using jsp,java,javascript and mysql as backend. i have to store the data from the customer order-sheets. in this business, they organize the garments(product) according to the colour.then each color is divided in to sizes like s,m,l,xl,xxl. example: mens brief-bule-s-1000 nos. mens brief-bule-m-1000 nos. mens brief-bule-l-1000 nos. mens brief-yellow-s-1000 nos. mens brief-yellow-m-1000 nos. mens brief-yellow-l-1000 nos. i have to split the data into multiple tables to avoid the redunduncy,and data repetetion. i split the data into these tables. orders; ordered_colors; ordered_sizes; ordered_items. the ordered_items table stores the relationship among the above entities and the order_quantity. because of the hierarchical nature ,i could not design the tables; to avoid multipe select statements and update statements(using jdbc,sql) during edit and list oprations. could you give a solution to design tables and the logic to get data using jdbc,sql. i waits for your kind suggestion and advice. thanking you sir. yours truly suresh

Answer Wiki

Thanks. We'll let you know when a new response is added.

Howdi,
I would design this using relational database design, not worrying about how they want to display the data.
i.e.
product
– product_id
– color_id
– size_id
– price, etc.

co_color
– color_id
– color_name

co_size
– size_id
– size_name

customer
– customer_id
– name, address, etc.

customer_order_product_list
– customer_product_list_id
– customer_id
– order_id
– product_id
– date_added
– price

order
– customer_id
– cust_product_list_id
– order_status, etc.

That type of thing. Write sql in order to get the views as far as the way they organize their data, or to deal with insertion of data.
If you do it in relational database design it will be much more flexible in the end. You can write sql to periodically move it into what is called flattened tables, which are the ways that the customer actually views the data, for easy access. Or you can write views as well to do the same thing if mysql has that capability.
Hope this helps,
-jim

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following