•
•
•
•
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 456,433 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,641 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: Programming Forums
Views: 3097 | Replies: 16 | Solved
 |
•
•
Join Date: Jun 2007
Location: Tampa, FL
Posts: 2
Reputation: 
Rep Power: 0
Solved Threads: 1
Newbie Poster
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).
Kelvin C Waters
•
•
Join Date: Jun 2007
Posts: 8
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by DenisOxon 
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....
Last edited by simward : Jul 5th, 2007 at 2:30 pm. Reason: Added something
•
•
Join Date: Jun 2007
Posts: 8
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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 :
But I dont think i am normalized.....
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 (idOrder, idProvider, dateOrder, ...) Order_Has_Products (idOrder, idProduct, quantity, ...) Product (idProduct, nameProduct, ....) Serial (idSerial, idOrder, idProduct, serial, ...) Underline : PK, Italic : FK
But I dont think i am normalized.....
•
•
Join Date: Jun 2007
Posts: 8
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Sorry, serials arent unique to each product, some companies give the same serial to a batch (cant edit the above post...)
•
•
Join Date: Jan 2007
Location: Oxfordshire, England
Posts: 307
Reputation:
Rep Power: 2
Solved Threads: 14
Posting Whiz
•
•
•
•
Originally Posted by 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 (idOrder, idProvider, dateOrder, ...) Order_Has_Products (idOrder, idProduct, quantity, ...) Product (idProduct, nameProduct, ....) Serial (idSerial, idOrder, idProduct, 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
•
•
Join Date: Jun 2007
Location: Tampa, FL
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 1
Newbie Poster
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
Last edited by Gameleech : Jul 6th, 2007 at 12:22 pm.
Kelvin C Waters
•
•
Join Date: Jun 2007
Posts: 8
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by 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
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
Last edited by simward : Jul 6th, 2007 at 1:25 pm.
|
•
•
•
•
•
•
•
•
DaniWeb Database Design Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- Previous Thread: want a search facility
- Next Thread: How to convince my boss that normalisation is best
