Re: design help

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 06:57:49 -0500

Yes, product and inventory would be related on ProductID. Since there could be multiple inventory checks per day the other PK column could be InventoryID OR LogDate could be be part of the PK.

Within the Defect table I am thinking that LogDate and ProductID would make up the PK. Then again, LogDate could simply be part of the table and another column (identity, for example) could be added the PK.

There is not a right or wrong way to do it...you just have to pick a method that works for you and meets your business requirements. 

-- 
Keith
"Hassan" <fatima_ja@hotmail.com> wrote in message news:uTiZuDlJEHA.3944@tk2msftngp13.phx.gbl...
> Does that mean the relation between product and inventory or product and
> defect might be a one to one relation
> 
> "Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message
> news:ugJKpikJEHA.2660@TK2MSFTNGP09.phx.gbl...
> It really depends on what you want to do, how much data your need to
> capture, and how you want to use the data.
> 
> It would seem that these are the bare minimum:
> Product table
> ProductID
> ProductName varchar
> 
> Inventory table
> InventoryID
> ProductID
> LogDate
> InventoryQty
> 
> Defect table
> ProductID
> LogDate
> DefectCode
> Explanation
> 
> 
> -- 
> Keith
> 
> 
> "Hassan" <fatima_ja@hotmail.com> wrote in message
> news:uwfz3XkJEHA.3120@TK2MSFTNGP11.phx.gbl...
> > I want to keep track of products , its inventory and any faults to the
> > products.. Can someone provide a broad design for this ? As far as table
> > names and their relationship to other tables. Doesnt have to be detailed
> and
> > does not have to include data types,etc.. Thanks
> >
> >
> 
>

Relevant Pages

  • Re: Product inventory table design
    ... Inventory might not be appropriate). ... FK // The combination BranchId and ProductId would be ... putting the ProductID field in tblInventory does nothing to make it easier ... pcs, on the shelf ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Product inventory table design
    ... By putting ProductId in the InventoryDetail, an inventory will include the counting of several products, instead of one product per inventory counting. ... putting the ProductID field in tblInventory does nothing to make it easier ... pcs, on the shelf ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Product inventory table design
    ... By putting ProductId in the InventoryDetail, ... pcs, on the shelf ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Product inventory table design
    ... The inventory in each branch is done by cycle counting. ... What I have done is just shifting the foreign key ProductId in InventoryDetail to Inventory: ... pcs, on the shelf ... a customer has reserved these ...
    (microsoft.public.access.tablesdbdesign)
  • Running totals in a query
    ... Group by [ProductID] ... Change them to suit ... >I am trying to build a query to only show the inventory ...
    (microsoft.public.access.queries)