Designing inventory system

Hi

Good Day!

I am designing a database to manage an Inventory system. I have two tables in it - Receives and Issues with two common fields -ProductID and Quantity.

I need some facilities in the database -

# Current Stock - Two fields - ProductID & BalanceQty. I want to get the balance quantity for a given product.

# Product Ledger - I want to have a ledger of product transaction with Date, ReceiveQty, IssueQty, Balance.

How can I have these facilities? Should I use a SProc or View? I need some expert openion. Please give me some details example. I am very new in SQLServer.

Thnk you very much!

Regards

Kapalic

Your question isn't really very clear to me, but it is really down to what you are using the data for. I would say stored procedure if this is used in a user interface (particularly in a single location), or a view if you want to be able to access the data in multiple locations, or perhaps for ad-hoc access...

I am in favor of using stored procedures for all access to the database in your user interfaces to make things easier to maintain and use.|||

I agree with Louis. A stored procedure will be easier to maintain and easier to secure.

You definitely want the [CurrentStock].[BalanceQty] values to be dynamically calculated -and not static data stored in a table.

|||

You can use a stroed procedure where you can pass the ProductId & you can get the BalanceQty & other details - it should be handy

|||

Thnx guys for ur answers. Actually I couldn't make myself clear.

Can anyone please give me an example how to write these sprocs? I need to check if enough product exist in the receive table before issueing a product in the issue table.

Thnx!

|||

If you mean protecting against over promising stock, if the tables are in the same database, I would use two techniques:

1. Stored procedure to calculate available stock when the user asks for the list (like when a user of Amazon goes to the page of a product and it says they have 3 in stock

2. A trigger that prevents an order from being placed without stock being available.

The second one will be a bit, because you will likely want to let the user backorder a product, but your order item for a product should be clearly marked as that, so the user doesn't get stuck waiting forever. So the calculation of available stock must take into consideration:

1. Will this product be stocked again (status of the product)

2. It there stock to cover the order now, even if there was when the user chose to order it, if not reject the line item, unless the user has specifically said "okay to back order"

But I would use a trigger to make sure that the status of the line item matched the expectation of order.

|||

Thnx Buddys!

I've written a view to show all the product with current stocks (PurchaseQty - IssueQty - DamageQty). And a sproc to wrap the view.

But I can't get an way to show a ledger for individual products, like -

Date Product PurchaseQty IssueQty DamageQty BalanceQty

Regards

Kapalic

|||

Use a WHERE clause, passing in the ProductID -both in the stored procedure and the VIEW.

Very similar to the code we wrote for you last week.

Designing inventory system

Hi

Good Day!

I am designing a database to manage an Inventory system. I have two tables in it - Receives and Issues with two common fields -ProductID and Quantity.

I need some facilities in the database -

# Current Stock - Two fields - ProductID & BalanceQty. I want to get the balance quantity for a given product.

# Product Ledger - I want to have a ledger of product transaction with Date, ReceiveQty, IssueQty, Balance.

How can I have these facilities? Should I use a SProc or View? I need some expert openion. Please give me some details example. I am very new in SQLServer.

Thnk you very much!

Regards

Kapalic

Your question isn't really very clear to me, but it is really down to what you are using the data for. I would say stored procedure if this is used in a user interface (particularly in a single location), or a view if you want to be able to access the data in multiple locations, or perhaps for ad-hoc access...

I am in favor of using stored procedures for all access to the database in your user interfaces to make things easier to maintain and use.|||

I agree with Louis. A stored procedure will be easier to maintain and easier to secure.

You definitely want the [CurrentStock].[BalanceQty] values to be dynamically calculated -and not static data stored in a table.

|||

You can use a stroed procedure where you can pass the ProductId & you can get the BalanceQty & other details - it should be handy

|||

Thnx guys for ur answers. Actually I couldn't make myself clear.

Can anyone please give me an example how to write these sprocs? I need to check if enough product exist in the receive table before issueing a product in the issue table.

Thnx!

|||

If you mean protecting against over promising stock, if the tables are in the same database, I would use two techniques:

1. Stored procedure to calculate available stock when the user asks for the list (like when a user of Amazon goes to the page of a product and it says they have 3 in stock

2. A trigger that prevents an order from being placed without stock being available.

The second one will be a bit, because you will likely want to let the user backorder a product, but your order item for a product should be clearly marked as that, so the user doesn't get stuck waiting forever. So the calculation of available stock must take into consideration:

1. Will this product be stocked again (status of the product)

2. It there stock to cover the order now, even if there was when the user chose to order it, if not reject the line item, unless the user has specifically said "okay to back order"

But I would use a trigger to make sure that the status of the line item matched the expectation of order.

|||

Thnx Buddys!

I've written a view to show all the product with current stocks (PurchaseQty - IssueQty - DamageQty). And a sproc to wrap the view.

But I can't get an way to show a ledger for individual products, like -

Date Product PurchaseQty IssueQty DamageQty BalanceQty

Regards

Kapalic

|||

Use a WHERE clause, passing in the ProductID -both in the stored procedure and the VIEW.

Very similar to the code we wrote for you last week.

Designing category system

Hi,

I am trying to design tables for managing category system.

In my inventory management system, I have a receive form, where I have to enter a product from a subcategory of a category.

I can't figuire out how should i design my tables. Am I correct with these tables -

Products Table - ProdID, ProdName, CatID, SCatID
Category Table - CatID, CatName
SubCategory Table - SCatID, SCatName

I can't show only the subCategories associated with a Category. What will be the design?

Also in the Receive form, which one will be logical? Should I select a category first, and related subCategories appears, and then product list form that subcategory appear?

Or should I select the product first, then categories and subCategories should appear?

Thanx a lot in advance for your help.

Regards
Kapalic

It depends how your hierarchy is,
suppose if your hierarchy follows ,

Category
|- Sub-Category
|- Product

Then you can go for following design,

Category -> CatId, CatName, ParentCatId
Where
CatId Primary key
ParentCatId foreign Key of CatId

(if ParentCatid is null then Category otherwise Sub Category)

Product -> ProductId, CatId
Where
CatId is Foregitn key of Category(CatId)

Regarding selection,
Product -> Sub-Category is ONE ON ONE relation
Sub-Cateogry -> Category is ONE ON ONE Relation

But,
Category -> Sub-Category is ONE To MANY relation
Sub-Cateogry -> Product is ONE ON MANY relation

Again its depends how your business rules defines.

|||

Hi ManiD,

Thnx for your reply! I will use the table structure u provided. This is much more logical.

And regarding to choosing fields, i do not have any binding other than existance of category and subcategory id. I just want an expert openion on which approach will be better for the users and developers perspective.

Regards
Kapalic

Design Q

I'm going to create a hardware/software inventory program to keep track on all the computers at the office. This program should be able to search the database using queries like "all computers with a cpu faster than 500Mhz".

I want some tips on my database design.

One solution is to create columns for each piece of hardware, i.e. cpu, ram, hdd etc etc. Then just run simple SELECT queries against them. The problem is that a computer may have many HDDs/CDs etc, and also other type of equipment may be entered in the database like switches and routers.

Another solution is to specify the valuetype + value in one table having a relation to another table containing the actual machines/routers/switches. The problem here is that I can't do numeric comparisons this way since "11" is less than "2", ("all computers with a cpu faster than 500Mhz").

Any suggestions?Table Computer
(ComputerID
...)

Table HardwareType
(TypeID
Description)

Table ComputerHardware
(ComputerID
TypeID
Description
NumericValue NULL)

Only use the NumericValue for items where it's relevant, like CPU speed or HDD size and not for things like Keyboards.|||How would I write my SQL statement to get the SQL Server to treat the values as Numeric then?|||They should be stored as numeric values. That's the column 'NumericValue'...

Make it a decimal 18,4 or something similar

design help

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
It really depends on what you want to do, how much data your need to =
capture, and how you want to use the data. =20
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
--=20
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
>=20
>
|||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
>
|||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. =20
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.
--=20
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[vbcol=seagreen]
> defect might be a one to one relation
>=20
> "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.
>=20
> It would seem that these are the bare minimum:
> Product table
> ProductID
> ProductName varchar
>=20
> Inventory table
> InventoryID
> ProductID
> LogDate
> InventoryQty
>=20
> Defect table
> ProductID
> LogDate
> DefectCode
> Explanation
>=20
>=20
> --=20
> Keith
>=20
>=20
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uwfz3XkJEHA.3120@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
table[vbcol=seagreen]
detailed
> and
>=20
>

design help

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.. ThanksIt really depends on what you want to do, how much data your need to =
capture, and how you want to use the data. =20
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
--=20
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
>=20
>|||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
>|||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. =20
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.
--=20
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
>=20
> "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.
>=20
> It would seem that these are the bare minimum:
> Product table
> ProductID
> ProductName varchar
>=20
> Inventory table
> InventoryID
> ProductID
> LogDate
> InventoryQty
>=20
> Defect table
> ProductID
> LogDate
> DefectCode
> Explanation
>=20
>=20
> --=20
> Keith
>=20
>=20
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uwfz3XkJEHA.3120@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
table[vbcol=seagreen]
detailed[vbcol=seagreen]
> and
>=20
>

design help

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.. ThanksIt 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
> >|||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
>|||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
> >
> >
> >

design best practices on series number

Hi,

I need to design a table header for inventory transactions with the specifications as follows:

1. System-generated series numbers (integer)

2. Series numbers must be unique by branch by transaction type. Thus if I have following:

Branches: Br1, Br2

Transaction Type: SRS (Stock Receipt from Supplier), SRB(.. from Branch)

The series number must be implemented in such a way that,

Br1 SRS 0000000001

Br1 SRB 0000000001

Br2 SRS 0000000001

Br2 SRB 0000000001

Then, in every INSERT, series number should be incremented by 1, grouped by branch by transaction type. That is, after INSERT with Br1/SRB the figure may now look like,

Br1 SRS 0000000001

Br1 SRB 0000000002

How do I design my table in order to achieve this? Note that this table header will have a detail (master/detail) referenced by foreign key.

Thanks in advance.

What I have come up so far are the following:

1. Create an identity field which will be the designated PK for the table header.

2. Branch, Trx_Type, SeriesNum will be a compounded index with a unique constraint.

3. In the branch office, there will be a shared text file containing the last series number for the branch so that, upon saving the transaction (setup is real-time online), the system will get the last series number from the file then increment by 1 and use it as the series number for the transaction. Upon completion, the system will update the file with the new last series number.

I need your comments on this, and if you have a better solution, pls let me know.