database design-2

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.

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

– color_id
– color_name

– size_id
– size_name

– customer_id
– name, address, etc.

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

– 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,

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.

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.

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


Share this item with your network: