Design Advice - Need ASAP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Design Advice - Need ASAP

Author

Topic

JayDial
Starting Member

2 Posts

Posted - 2007-11-27 : 16:01:28

Hello Everyone. Im sorry for this urgent post, but have critical issue that needs a solution quick. So for my issue. I am adjusting our sales order tables to handle a couple different scenarios. Currently we have 2 tables for sales orders

SALESORDERS
------------
SORDERNBR int PK,
{ Addtl Header Columns... }

SALESORDERDETAILS
-------------------
SODETAILID int,
SORDERNBR int FK,
PN varchar,
SN varchar(25),
{ Addtl Detail Columns ... }

Currently the sales order line item is serial number specific. I need to change the tables to be able to handle different requests like :

Line Item Request ( PN, QTY )
Line Item Request ( SN )
Line Item Request ( PN, GRADE, QTY )
ETC.

I am thinking i need to create a new table to hold the specifics for a particular line item. Maybe like this :

SALESORDERSPECS
----------------
SOSPECID int,
SODETAILID int FK,
SPECTYPE varchar, IE : SN, PN, GRADE. { one value per row }
SPECVALUE varchar IE : GRADE A

Im thinking i would need to rename the SALESORDERDETAILS table to SALESORDERITEMS. SALESORDERITEMS would just contain header info like
SalePrice,
Warranty,
Etc...

Then rename SALESORDERSPECS to SALESORDERDETAILS...

Anyone understand what im trying to do? If you need more info please ask. You can also get a hold of me through IM.

Thanks!

JayDial

JP Boodhoo is my idol.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-27 : 16:17:20

in your details table you have records like this?
order1 partnumber5 serialnumberA1 gradeB
order1 partnumber5 serialnumberA2 gradeB
order1 partnumber5 serialnumberA3 gradeB
order1 partnumber9 serialnumberX1 gradeA
order1 partnumber9 serialnumberX2 gradeA
order1 partnumber9 serialnumberX3 gradeA

and you have performance/reporting problems when searching for partnumber5, count(distinct ordernumber)?

and you think you want to creat an associative table to separate the records like this?
lineitem1, order1, partnumber5, gradeB
lineitem1, order2, partnumber9, gradeA

lineitem1, order1, serialnumberA1
lineitem1, order1, serialnumberA2
lineitem1, order1, serialnumberA3
lineitem1, order2, serialnumberX1
lineitem1, order2, serialnumberX2
lineitem1, order2, serialnumberX3

JayDial
Starting Member

2 Posts

Posted - 2007-11-27 : 16:47:46

I figured this would be hard to describe... Ok im only going to focus on the sales order details table.. Currently it holds a specific serial number that is tied to a line item. The issue with this is that if the sales reps dont care about a particular serial number and only want a specific part number they dont have this option. They are forced to pick a serial number that is of the part number they need. This causes issues in the warehouse because if the serial number they picked is buried on a pallet then it takes more time to dig it out.

What we need to happen is allow the sales rep to put in varying line item criteria like :

I need 10 pieces of Part Number XYZ and it needs to be Grade B.
I need 1 Piece of SerialNumber 9288DJ233
I need 1 Piece of SerialNumber 213892839 and it needs to be PN ZYX

The advice i need is how to store these varying line item requirements.

Let me know if further explanation is needed.

J

JP Boodhoo is my idol.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 10:08:49

if a lineitem can be for multiple quantities and each one can have a serial number, you should create another table. one with everything that exists only once for a lineitem (qty, partnumber, grade?) and one with everything that exists only once for a product (serial number, color).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources