Ad:

Database Design Discussion Thread
Marked Solved
Views: 4653

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Jun 21st, 2007

Novice Table Relation Question

Expand Post »

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!

Last edited by simward; Jun 21st, 2007 at 8:11 pm.

simward

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Jun 2007

Permalink

Jun 25th, 2007

Re: Novice Table Relation Question

use foreign key concept.

ithelp

Reputation Points: 769

Solved Threads: 128

Banned

Offline

1,910 posts
since May 2006

Permalink

Jun 26th, 2007

Re: Novice Table Relation Question

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

sibir1us

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

30 posts
since Jun 2007

Permalink

Jun 27th, 2007

Re: Novice Table Relation Question

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

(Toggle Plain Text)

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....

simward

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Jun 2007

Permalink

Jun 27th, 2007

Re: Novice Table Relation Question

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.

sibir1us

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

30 posts
since Jun 2007

Permalink

Jun 27th, 2007

Re: Novice Table Relation Question

Indeed

simward

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Jun 2007

Permalink

Jun 28th, 2007

Re: Novice Table Relation Question

Click to Expand / Collapse Quote 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

DenisOxon

Reputation Points: 22

Solved Threads: 19

Posting Whiz

Offline

345 posts
since Jan 2007

Permalink

Jun 29th, 2007

Re: Novice Table Relation Question

Denis' solution is good. Do you still have questions or did you find your way through the jungle of database design? :-)

sibir1us

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

30 posts
since Jun 2007

Permalink

Jun 29th, 2007

Re: Novice Table Relation Question

Thanks Sibir1us

DenisOxon

Reputation Points: 22

Solved Threads: 19

Posting Whiz

Offline

345 posts
since Jan 2007

Permalink

Jul 2nd, 2007

Re: Novice Table Relation Question

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

simward

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Jun 2007

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Database Design Forum Timeline: want a search facility

Next Thread in Database Design Forum Timeline: 2NF and Multi-candidate Keys

DaniWeb Message

Cancel Changes

User Name
Password