•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Database Design section within the Web Development category of DaniWeb, a massive community of 375,170 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,237 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Database Design advertiser:
Views: 2649 | Replies: 16 | Solved
 |
•
•
Join Date: Jun 2007
Posts: 8
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Novice Table Relation Question Hi everyone, this is my first post here!
I am making a small application that helps manage merchandise returns in a computer store I work for.
I am currently designing the database for it and an issue has arose which is probably obvious but is eluding me right now.
I have a bunch of tables (clients, inquiries, orders, etc...). An inquiry is when a client brings his defective product for replacement. In order to track products we sell we also store our orders in the same application (to make sure someone isn't bringing a product we didn't sell to him and to know if an ordered product was then replaced later). Each item we order has a serial number (which doesn't need to be unique since some companies use a general serial for a hole batch).
I did a rough beta about year ago of the app and the way it worked back then was that i had an orders table, storing relevant dates and info, and one column was a longtext and i would just store (in way similar to serialization) the hole order in it and then just get it back in queries and the app would reverse the serialization, hence giving me my order with all the appropriate serial numbers (the concept of a table in a longtext field...). I know that wasn't a good idea.... A bunch of problems ensued and made my life debugging the rough beta hell.
I am redesigning it now with better techniques i recently learned (DPs, DB modeling, etc...)
Ok, now you are in context, if you've read so far...
My problem is, how do I design the DB so my "orders" table can have a relation to another table named "products" that contains general information that is specific to the product (name, SKU (company product number), etc...) while at the same time store each specific serial number for each piece ordered in that order....
The only solution I come up with is just to make a product in the product table with that serial number but then how do i associate all those to the orders table and the product table will get pretty big pretty fast this way....
I use a MySQL 5.0 database
Thx for your time!
I am making a small application that helps manage merchandise returns in a computer store I work for.
I am currently designing the database for it and an issue has arose which is probably obvious but is eluding me right now.
I have a bunch of tables (clients, inquiries, orders, etc...). An inquiry is when a client brings his defective product for replacement. In order to track products we sell we also store our orders in the same application (to make sure someone isn't bringing a product we didn't sell to him and to know if an ordered product was then replaced later). Each item we order has a serial number (which doesn't need to be unique since some companies use a general serial for a hole batch).
I did a rough beta about year ago of the app and the way it worked back then was that i had an orders table, storing relevant dates and info, and one column was a longtext and i would just store (in way similar to serialization) the hole order in it and then just get it back in queries and the app would reverse the serialization, hence giving me my order with all the appropriate serial numbers (the concept of a table in a longtext field...). I know that wasn't a good idea.... A bunch of problems ensued and made my life debugging the rough beta hell.
I am redesigning it now with better techniques i recently learned (DPs, DB modeling, etc...)
Ok, now you are in context, if you've read so far...
My problem is, how do I design the DB so my "orders" table can have a relation to another table named "products" that contains general information that is specific to the product (name, SKU (company product number), etc...) while at the same time store each specific serial number for each piece ordered in that order....
The only solution I come up with is just to make a product in the product table with that serial number but then how do i associate all those to the orders table and the product table will get pretty big pretty fast this way....
I use a MySQL 5.0 database
Thx for your time!
Last edited by simward : Jun 21st, 2007 at 7:11 pm.
•
•
Join Date: May 2006
Location: ★★ijug.net★★
Posts: 817
Reputation:
Rep Power: 4
Solved Threads: 61
Practically a Posting Shark
use foreign key concept.
★★ Homework Help ★★ Freelance Programmer ★★ Freelance Jobs ★★
★★ Build A Website ★★ Interview Question And Answer ★★ Tax Filing ★★
★★ Build A Website ★★ Interview Question And Answer ★★ Tax Filing ★★
•
•
Join Date: Jun 2007
Location: Los Angeles, CA
Posts: 30
Reputation:
Rep Power: 2
Solved Threads: 2
Light Poster
Well, think of it this way: there are several types of relations between objects - one to one, one to many, many to many. Imagine, lets say, i would like to represent a database model for your store. I will have a table "Clients" (with fields: ClientId[int, primary key], ClientName[varchar50], ClientEmail[varchar50]...etc ) and another table "Orders"(with fields: OrderId[int, primary key], ClientId[int, foreign key], OrderName[varchar50]... etc ). Now, here we come to the point why it is called "relational database": by creating different types of relations between tables you represent a highly cohesive model. This means that you create a certain record once, and from then on, this record is represented by an Id adn this way you save alot of space (the data type of integer is way smaller, compared to the repetition of the data). Here is one final example: in your model most likely you will have an "one-to-many" relationship between a client and an order, which means, that one client can have many orders, but one order cannot have to clients. There for, "Clients" table will be parent to "Orders", and they will have a relationship on "ClientId". Hope this helps. Feodor
•
•
Join Date: Jun 2007
Posts: 8
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Sorry, I must of missed mentioning I already have learned how to model databases... But since i just finished the course I am still having a little trouble applying it to real life situations. So I am still faced with the problem I mentioned earlier. Let me post a rough view of my tables
Dictionary of terms :
RMA : Return Merchandise Authorization, term used by providers and resellers for the hole operation regarding the replacement of a defective product
Provider : Providers sell products to us and we sell those products to consumers
SKU : Dunno what it stands for, but it's the ID of a product from the providers for fast searches in their databases.
Relationships :
A RMA has ONLY 1 customer requesting a replacement.
A RMA has ONLY 1 product at a time.
A customer can have MANY RMAs if he has MANY products.
A product can come from MANY different providers.
An order has MANY products and ONLY 1 provider
Relationships I am having trouble with :
An order has MANY products and each product in the order has his "UNIQUE" serial number (different products could have the same Serial)
A product can have MANY SKUs since it can have MANY providers.
So? Please help me on how to make these relations... I've been scratching my head for a while now....
customer(idCustomer, nameClient, firstNameClient, ...) product(idProduct, nameProduct, ...) rma(idRma, registeredDate, idCustomer_customer, idProduct_product) provider(idProvider, nameProvider, ...) order(idOrder, registeredDate, receiptDate, ...) NOTE 1 : Underline means primary key, Italic is for foreings NOTE 2 : I did not include the field for serial numbers since I have no idea how to make it work....
Dictionary of terms :
RMA : Return Merchandise Authorization, term used by providers and resellers for the hole operation regarding the replacement of a defective product
Provider : Providers sell products to us and we sell those products to consumers
SKU : Dunno what it stands for, but it's the ID of a product from the providers for fast searches in their databases.
Relationships :
A RMA has ONLY 1 customer requesting a replacement.
A RMA has ONLY 1 product at a time.
A customer can have MANY RMAs if he has MANY products.
A product can come from MANY different providers.
An order has MANY products and ONLY 1 provider
Relationships I am having trouble with :
An order has MANY products and each product in the order has his "UNIQUE" serial number (different products could have the same Serial)
A product can have MANY SKUs since it can have MANY providers.
So? Please help me on how to make these relations... I've been scratching my head for a while now....
•
•
Join Date: Jun 2007
Location: Los Angeles, CA
Posts: 30
Reputation:
Rep Power: 2
Solved Threads: 2
Light Poster
Reading a book or taking a course plays a small role in your way to being a database designer. Touching the problem, getiing your hands "messy" with database modeling is what will make you a good developer. My grandma (may she rest in peace) used to say: "If you could learn just by looking, then the butcher's dog would have become a butcher by now...". What she meant by this was, that one needs to gain the knowledge by experience and by his own curiosity.
******************
15 minutes of research can save you 50% or more. forum.feodorgeorgiev.com
***Need Web Hosting?
******************
15 minutes of research can save you 50% or more. forum.feodorgeorgiev.com
***Need Web Hosting?
******************
•
•
Join Date: Jan 2007
Location: Oxfordshire, England
Posts: 307
Reputation:
Rep Power: 2
Solved Threads: 14
Posting Whiz
•
•
•
•
Originally Posted by simward 
[code]
customer(idCustomer, nameClient, firstNameClient, ...)
product(idProduct, nameProduct, ...)
rma(idRma, registeredDate, idCustomer_customer, idProduct_product)
provider(idProvider, nameProvider, ...)
order(idOrder, registeredDate, receiptDate, ...)
An order has MANY products and each product in the order has his "UNIQUE" serial number (different products could have the same Serial)
...
Hi,
One way would be to
a) add customer id to order table - at moment you are not linking order to customr.
b) build another table called orderdetails which has following fields
idOrder, idProduct, Serial number (one record per item)
Good luck
Denis
•
•
Join Date: Jun 2007
Location: Los Angeles, CA
Posts: 30
Reputation:
Rep Power: 2
Solved Threads: 2
Light Poster
Denis' solution is good. Do you still have questions or did you find your way through the jungle of database design? :-)
******************
15 minutes of research can save you 50% or more. forum.feodorgeorgiev.com
***Need Web Hosting?
******************
15 minutes of research can save you 50% or more. forum.feodorgeorgiev.com
***Need Web Hosting?
******************
•
•
Join Date: Jan 2007
Location: Oxfordshire, England
Posts: 307
Reputation:
Rep Power: 2
Solved Threads: 14
Posting Whiz
Thanks Sibir1us
•
•
Join Date: Jun 2007
Posts: 8
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
I believe this solution is good, very good
I am sorry i couldn't answer earlier i have been travelling for the last few days and internet wasn't readily available
I will be back tomorrow and i will test this concept with my system
Thank you for your help so far, expect feedback soon and I will mark the issue as solved once i'm done trying it out
PS : Customers have no links to Orders....orders are products ordered from providers that are sold later on to customers, the selling is handled by Forune 1000's Acomba, a financial management application. We only track the orders in my app because we need to make sure we actually sold the product with that serial number to this customer, because the app can in some cases automaticly prepare shipping to the provider, and if a new product is sent as replacement (hence the serial changed) we can track the hole history. Although it doesnt make your solution invalid at all
I am sorry i couldn't answer earlier i have been travelling for the last few days and internet wasn't readily available
I will be back tomorrow and i will test this concept with my system
Thank you for your help so far, expect feedback soon and I will mark the issue as solved once i'm done trying it out
PS : Customers have no links to Orders....orders are products ordered from providers that are sold later on to customers, the selling is handled by Forune 1000's Acomba, a financial management application. We only track the orders in my app because we need to make sure we actually sold the product with that serial number to this customer, because the app can in some cases automaticly prepare shipping to the provider, and if a new product is sent as replacement (hence the serial changed) we can track the hole history. Although it doesnt make your solution invalid at all
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb Database Design Marketplace
Linear Mode
- Previous Thread: want a search facility
- Next Thread: How to convince my boss that normalisation is best
