Re: Database Design Problem

From: Daniel (danny_at_noemail.com)
Date: 04/28/04

Next message: Zach Wells: "Re: Table Design Question"
Previous message: Max: "Please need help with Cross Join query"
In reply to: Greg Obleshchuk: "Re: Database Design Problem"
Next in thread: Joe Celko: "Re: Database Design Problem"
Messages sorted by: [ date ] [ thread ]

Date: Wed, 28 Apr 2004 08:48:06 -0600

I agree with you. The problem I have with putting the Product Catagory in
the Products tables is because the products table is not the only products
table. The products table is broken into three products tables because no
one product table could map all three. So, I decided to create a product
catagory table which has a field tableName which tells which products table
is related to the productCatagoryID. This way when I retrieve the order I
can see which table to grab the product table and vice versa on the
insert/update.

Thanks

"Greg Obleshchuk" <greg-n-o-s-p-a-m-@ag-s-o-f-t-w-a-r-e.com> wrote in
message news:%23scHifLLEHA.3664@TK2MSFTNGP10.phx.gbl...
> 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: Zach Wells: "Re: Table Design Question"
Previous message: Max: "Please need help with Cross Join query"
In reply to: Greg Obleshchuk: "Re: Database Design Problem"
Next in thread: Joe Celko: "Re: Database Design Problem"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: Table schema for user login system?
... good design dictates that I should. ... Definitely not normalization. ... this is not the correct newsgroup for discussion database design. ...
(comp.lang.php)
Foreign Key Design...
... I am relatively new to database design and wanted to ask a few ... normalization. ... have any relationship between the Country and PostCode tables. ...
(comp.databases.ms-access)
Re: Conceptual design advice for relational database
... violates the first rule of normalization. ... Get a copy of "Database Design for Mere Mortals" by Michael Hernandez. ... Access Database Samples: www.rogersaccesslibrary.com ... Want answers to your Access questions in your Email? ...
(microsoft.public.access.tablesdbdesign)
Re: Database Design & Normalization Question
... Products, Other Titles, Title Types. ... However, oftentimes bad design will kill performance, no matter ... Normalization would dictate that these dates would depend on the KEY, ... > didn't have to change the database design to store new ...
(microsoft.public.sqlserver.server)
Re: Use relationship or not?
... If you understand normalization, ... A diver can only where 1 suit at a time. ... many dives. ... wide-flat design as you suggest. ...
(microsoft.public.access.modulesdaovba)