Re: Database Design Problem
From: Greg Obleshchuk (greg-n-o-s-p-a-m-_at_ag-s-o-f-t-w-a-r-e.com)
Date: 04/28/04
- Next message: Yaz: "RE: error: saving to a ntext column becomes corrupted (???)"
- Previous message: Aaron Bertrand [MVP]: "Re: How to ALTER COLUMN from text to varchar? - Thanks"
- In reply to: Daniel: "Re: Database Design Problem"
- Next in thread: Daniel: "Re: Database Design Problem"
- Reply: Daniel: "Re: Database Design Problem"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 28 Apr 2004 10:29:05 +1000
Hi Daniel,
>>What do you think about how I use the ProductCatagoryID?
The way I think about it is that a product can below to one category, so it
should be a member of the products table not orders or orders details.
Design is a hard thing to do if you want a good design that is. It hard
because it is the single most important part of the application. With a
good design the application almost writes itself. When you have problems
with application Interfaces in database based application it is normally
because of bad database design. There are lots of normalization rules
governing design but if you take your time and use common sense then you
normally can do a good job.
When I design I normally just write out all the information I want to
collect and store on line big line.
OrderNumber,
ORderDate,..ProductName,#Products,Cost,DIscount,..CustomerName,Customeraddre
ss
That type of thing
Then I spilt it up into sections or tables removing repeative data
Orders , OrderDetails, Products, Customers
And so on from there.
-- I hope this helps -------------------------------- Greg O Looking for SQL Scripts, Need Help? http://www.SQL-Scripts.com Document all your databases now. http://www.ag-software.com/ags_scribe_index.aspx Crosstab queries from SQL Server. http://www.ag-software.com/xp_ags_crosstab.aspx Edit Extended Properties as easy as can be. http://www.ag-software.com/ags_SSEPE_index.aspx "Daniel" <dan@spam.com> wrote in message news:OvAC0ZGLEHA.556@TK2MSFTNGP10.phx.gbl... > Greg, > > Thanks, and why I include the customer PO in the OrderDetails is because > there are times when the PO can be different for each line item (does not > happen very often). I include the ProductCatagoryID in the Order table is > because this is used to relate to a reference table which tells the client > app which products table to save the data to. I did not mention that this > db represents three products which are compeletly different. Each product > has its own table and child tables. And I use the productcatagroyID as a > reference to the correct product tables and I should move it to the order > details table. Database Design is very difficult for me because I do not do > alot of it. I think your suggestions are good and will model the order very > well. What do you think about how I use the ProductCatagoryID? > > Thanks again > "Greg Obleshchuk" <greg-n-o-s-p-a-m-@ag-s-o-f-t-w-a-r-e.com> wrote in > message news:ezFZim%23KEHA.2556@TK2MSFTNGP11.phx.gbl... > > Hi Daniel, > > No you are on the right track. > > You have an order table because there is an order and a order it placed on > a > > certain date and in your case is for a certain customer > > > > You have an orderdetails table because each order can consist of many > > details lines. Moving the shipdate to the orderdetails is perfectly > normal > > because in the real world this happens. I would have thought though > looking > > at your design that you would lose ProductCategoryID from the Orders > table > > as this sounds like it relates to a product and should be in the products > > table. You should also have a customer and employee table and only > include > > the key to these tables in the orders table. So something like this > > > > CREATE TABLE [dbo].[Orders] ( > > [OrderID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > > [EmployeeID [INT] NOT NULL , > > [CustomerID [INT] NOT NULL , > > [TagID] [int] NULL , > > ) ON [PRIMARY] > > GO > > > > CREATE TABLE [dbo].[Order Details] ( > > [SerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [OrderID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > > [ProductID] [int] NOT NULL , > > [CustomerPO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [Quantity] [int] NULL , > > [ShipDate] [datetime] NULL > > [Instructions] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > > > > ) ON [PRIMARY] > > GO > > > > You would also have primarykeys on OrderID in ORders and SerialNumber in > > Orders Details > > > > Looking at CustomerPO why would you include this in the details table, I > am > > asumming there is only one purchase order per order > > > > > > -- > > I hope this helps > > -------------------------------- > > Greg O > > Looking for SQL Scripts, Need Help? http://www.SQL-Scripts.com > > Document all your databases now. > > http://www.ag-software.com/ags_scribe_index.aspx > > Crosstab queries from SQL Server. > > http://www.ag-software.com/xp_ags_crosstab.aspx > > Edit Extended Properties as easy as can be. > > http://www.ag-software.com/ags_SSEPE_index.aspx > > > > > > > > "Daniel" <dan@spam.com> wrote in message > > news:u57D9L#KEHA.2952@TK2MSFTNGP10.phx.gbl... > > > Hello, > > > > > > I have a question on how to map a Order and Order Details relationship. > > In > > > the Orders table I have an OrderID key and some other customer info > (Name, > > > Ship Date...). The Order Details uses OrderID as the key because there > is > > > one to many relationship with Order. The problem I am running into is > > there > > > is another field (Serial) which is a unique to each line item(order > > details > > > record) for the Order. When I redesign my Order and Order Details > tables, > > > the Order table only contains Customer Name and a couple of other > details > > > (employee). I had to move ShipDate and couple of other cusotmer details > > to > > > Order Details because each line item can have different ship date and so > > on. > > > I am just wondering is it worth having an Order table or maybe I can > > create > > > one table or map it another way? Here is the DDL of the two tables: > > > > > > CREATE TABLE [dbo].[Orders] ( > > > [OrderID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL > , > > > [ProductCatagoryID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > > > NULL , > > > [EmployeeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > > > [TagID] [int] NULL , > > > [CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > > > ) ON [PRIMARY] > > > GO > > > > > > CREATE TABLE [dbo].[Order Details] ( > > > [OrderID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL > , > > > [ProductID] [int] NOT NULL , > > > [SerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > > > [CustomerPO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > > [Quantity] [int] NULL , > > > [ShipDate] [datetime] NULL > > > [Instructions] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL > > > ) ON [PRIMARY] > > > GO > > > > > > > > > > > > > > >
- Next message: Yaz: "RE: error: saving to a ntext column becomes corrupted (???)"
- Previous message: Aaron Bertrand [MVP]: "Re: How to ALTER COLUMN from text to varchar? - Thanks"
- In reply to: Daniel: "Re: Database Design Problem"
- Next in thread: Daniel: "Re: Database Design Problem"
- Reply: Daniel: "Re: Database Design Problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
