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/26/04
- Next message: David Portas: "Re: query to calculate last date of each month?"
- Previous message: poseidon_at_online.nospam: "VDI fail to connect to Active/Passive Cluster using virtual server name"
- In reply to: Daniel: "Database Design Problem"
- Next in thread: Louis Davidson: "Re: Database Design Problem"
- Reply: Louis Davidson: "Re: Database Design Problem"
- Reply: Daniel: "Re: Database Design Problem"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 27 Apr 2004 09:53:01 +1000
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: David Portas: "Re: query to calculate last date of each month?"
- Previous message: poseidon_at_online.nospam: "VDI fail to connect to Active/Passive Cluster using virtual server name"
- In reply to: Daniel: "Database Design Problem"
- Next in thread: Louis Davidson: "Re: Database Design Problem"
- Reply: Louis Davidson: "Re: Database Design Problem"
- Reply: Daniel: "Re: Database Design Problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
