Novice Table Relation Question

[simward]

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!

[ithelp]

use foreign key concept.

[sibir1us]

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

[simward]

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

customer([U]idCustomer[/U], nameClient, firstNameClient, ...)
product([U]idProduct[/U], nameProduct, ...)
rma([U]idRma[/U], registeredDate, [I]idCustomer_customer[/I], [I]idProduct_product[/I])
provider([U]idProvider[/U], nameProvider, ...)
order([U]idOrder[/U], registeredDate, receiptDate, ...)

[I]
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....[/I]

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

[sibir1us]

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.

[simward]

Indeed

[DenisOxon]

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

[sibir1us]

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

[DenisOxon]

Thanks Sibir1us

[simward]

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 :P

[Gameleech]

Sounds like you need to make use of a foreign key in your products table from your customer table. Also what is the nature (datatype) of the serial accounts? Is this assigned from a DB or incremental? Not sure from your text. I like to work with MS Visio when working with databases you can get a visual conception of your schema before you commit plus visio has the ability to update the db directly (any ODBC or OLE database).

[simward]

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

K i see this working if i would order 1 product per order.... The issue is orders can contain 10 DVD burners and each burner has his unique serial....

OR! i could just put the serial number field as a longtext and store the serials in there.... But i doubt its the best solution....

[simward]

K i found something, i think it works, and i can narrow down the problem now.

SO the problem was, Order can contain multiple products, each products can have a certain quantity, and each of those specific products have a unique Serial number. I was stuck at how do i store serials appropriately.

I think the following design fixes my problem :

Order ([U]idOrder[/U], [I]idProvider[/I], dateOrder, ...)
Order_Has_Products ([I][U]idOrder[/U][/I], [I][U]idProduct[/U][/I], quantity, ...)
Product ([U]idProduct[/U], nameProduct, ....)
Serial ([U]idSerial[/U], [I]idOrder[/I], [I]idProduct[/I], serial, ...)

Underline : PK, Italic : FK

But I dont think i am normalized.....

[simward]

Sorry, serials arent unique to each product, some companies give the same serial to a batch (cant edit the above post...)

[DenisOxon]

K i found something, i think it works, and i can narrow down the problem now.

SO the problem was, Order can contain multiple products, each products can have a certain quantity, and each of those specific products have a unique Serial number. I was stuck at how do i store serials appropriately.

I think the following design fixes my problem :

Order ([U]idOrder[/U], [I]idProvider[/I], dateOrder, ...)
Order_Has_Products ([I][U]idOrder[/U][/I], [I][U]idProduct[/U][/I], quantity, ...)
Product ([U]idProduct[/U], nameProduct, ....)
Serial ([U]idSerial[/U], [I]idOrder[/I], [I]idProduct[/I], serial, ...)
 
Underline : PK, Italic : FK

But I dont think i am normalized.....

Firstly you don't need idSerial, as far as I can see.

SEcondly for those products without unique serial number, then I would attach a unique serial number which is what you record in Serial. I know it is more work at dispatch time but allows you to track correctly.

Denis

[Gameleech]

If the serial cant be unique then it cant and shoudn't be used. Seems like you solved your problem. Not sure about your naming convention though.. "idOrder" is not standard Camel casting OrderID, CustomerID are the standard forms. I dont know what your professor suggest but this is the industry standard form and should be used. Good Luck

[simward]

If the serial cant be unique then it cant and shoudn't be used. Seems like you solved your problem. Not sure about your naming convention though.. "idOrder" is not standard Camel casting OrderID, CustomerID are the standard forms. I dont know what your professor suggest but this is the industry standard form and should be used. Good Luck

Thank you for the naming convention note.

Serial codes are our only means to find if a product was sold by us. If a client comes in and wants to screw us over with a product that we didn't sell to him we cant have repairs/replacements for them.

We cannot identify batch serials of course but if a company provides batch serials, having replacements done is easy because batch serials are assigned to cheap electronics (mouse, small speakers, cheap web cams) and the company doesn't mind doing it even though we haven't bought that merchandise.

But you are right, serials not being unique makes it all too confusing, I guess the problem is solved soon, prbly will revert to a text field and just store them in there