So, I have a series of tables with different columns (but some
'common')

e.g.
Sales order items (oritem)
Process order stock requirements (orstkall)
Picking note items (pickitm)
Miscellaneous manual stock reservation (

Each table can provide a requirement for stock based on status of
item. I therefore have another table (allocation) which contains the
outstanding requirements for stock, from which I need to create a
relation back to each of these tables. Allocations for a particular
row in any of these tables are stripped and rebuilt frequently.

Each of these has a primary key of the same type (int)

A cutdown of allocation is shown below

This is causing us issues with software like crystal reports, where
one cannot include the metadata in the link without effort, and thus
'spurious' links are arising.

So, 1. would you strip the metadata from this table? and 2. How would
you approach removing the metadata from this table?

The ID column is not an identity column, I am populating it from
within the app (for reasons which I can't change), so I could tie all
the entities into one number stream and thus one ID field in
allocation.
However, this would give me 'gaps' in the ID of each of the other
tables. Would this affect the fill factor I should set on them for
efficient indexes?




DDL And such


CREATE TABLE [dbo].[allocation](
[alc_ent_name] [char](16) NOT NULL,
[alc_ent_id] [int] NOT NULL,
[alc_ent_seq] [int] NOT NULL,
[alc_ent_sub] [int] NOT NULL,
[alc_bom_id] [int] NOT NULL,
[alc_boi_seq] [int] NOT NULL,
[alc_alt_stk_id] [int] NOT NULL,
[alc_due] [datetime] NULL,
[alc_base_qty] [float] NULL,
CONSTRAINT [allocation_pk] PRIMARY KEY CLUSTERED
(
[alc_ent_name] ASC,
[alc_ent_id] ASC,
[alc_ent_seq] ASC,
[alc_ent_sub] ASC,
[alc_bom_id] ASC,
[alc_boi_seq] ASC,
[alc_alt_stk_id] ASC
))


insert into allocation values('SOE_ORITEM ',
102,1,2,0,0,0,'20081222',21)
insert into allocation values('PUE_ORSTKALL ',
107,1,2,0,0,0,'20090126',4)
insert into allocation values('PUE_ORSTKALL ',
107,2,2,0,0,0,'20090126',3)
insert into allocation values('PUE_ORSTKALL ',
107,3,2,0,0,0,'20090126',1)
insert into allocation values('PUE_ORSTKALL ',
109,1,2,0,0,0,'20090126',4)
insert into allocation values('DSE_PICKITM ',
109,1,0,0,0,0,'20081217',2)
insert into allocation values('SOE_ORITEM ',
109,1,0,0,0,0,'20090126',20)
insert into allocation values('SOE_ORITEM ',
109,2,0,0,0,0,'20090126',21)
insert into allocation values('PUE_ORSTKALL ',
109,2,2,0,0,0,'20090126',3)
insert into allocation values('PUE_ORSTKALL ',
109,3,2,0,0,0,'20090126',1)
insert into allocation values('DSE_PICKITM ',
110,1,0,0,0,0,'20081216',1)
insert into allocation values('DSE_PICKITM ',
111,1,0,0,0,0,'20081216',1)
insert into allocation values('SOE_ORITEM ',
111,2,0,0,0,0,'20090126',21)
insert into allocation values('DSE_PICKITM ',
116,1,0,0,0,0,'20090120',2)
insert into allocation values('DSE_PICKITM ',
117,1,0,0,0,0,'20090119',1)
insert into allocation values('DSE_PICKITM ',
118,1,0,0,0,0,'20090119',2)
insert into allocation values('DSE_PICKITM ',
119,1,0,0,0,0,'20090119',2)
insert into allocation values('SOE_ORITEM ',
119,1,0,0,0,0,'20090204',5)
insert into allocation values('DSE_PICKITM ',
121,1,0,0,0,0,'20090204',24)
insert into allocation values('DSE_PICKITM ',
121,2,0,0,0,0,'20090204',8)
insert into allocation values('DSE_PICKITM ',
121,3,0,0,0,0,'20090204',24)
insert into allocation values('PUE_ORSTKALL ',
122,1,0,0,0,0,'20090219',1)
insert into allocation values('PUE_ORSTKALL ',
123,1,0,0,0,0,'20090219',1)
insert into allocation values('DSE_PICKITM ',
123,1,0,0,0,0,'20090217',4)
insert into allocation values('SOE_ORITEM ',
123,1,0,0,0,0,'20090213',4)
insert into allocation values('DSE_PICKITM ',
123,2,0,0,0,0,'20090217',5)
insert into allocation values('PUE_ORSTKALL ',
124,1,0,0,0,0,'20090219',1)
insert into allocation values('PUE_ORSTKALL ',
125,1,0,0,0,0,'20090219',1)
insert into allocation values('DSE_PICKITM ',
126,1,0,0,0,0,'20090203',5)
insert into allocation values('DSE_PICKITM ',
127,1,0,0,0,0,'20090225',5)
insert into allocation values('SOE_ORITEM ',
127,1,0,0,0,0,'20090218',2)
insert into allocation values('DSE_PICKITM ',
129,1,0,0,0,0,'20090226',5)

Iain Sharp (iains (AT) pciltd (DOT) co.uk) writes:
If I understand this correctly, there is a a column in this allocation
table that specified which table the row refers to.

That is rarely a good design, and tend to cause problem. Really what
you should do is difficult to tell with so little information about
the full picture. And then cryptic column names do not help.

Am I right to understand that all these orstkall, picktim etc share a
common ID space?

My reflection is that either all these tables reflects distinct entities,
in which case there should be one allocation table per entity. Or
they are in fact all the same entity, or subtypes of one, in which case
there should be a common table which also owns the ID.

But as I said, with so little information it's very difficult to come
with design suggestions.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mon, 18 Jan 2010 22:47:40 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:


Each of these tables are different entities, in that one is items for
sale to customer, one is items to be used to make items for sale to
the customer, one is items reserved to prevent their sale/use.

What they all have in common is that they add up to the amount of
requirement for the material in the warehouse. So I need to be able to
sum them sensibly, and work backwards from this information to the
originating table.

A sales item will have many different columns from the production item
or the stock reservation item, but they all have the same behaviour in
common. They all reserve material for use later.

This reservation data is then examined either in summary (add up all
the reservations for a stock item) or detail ( show me the
reservations of all types for this stock item, in due date order), or
a bit of both, (add up all the reservations for this stock item due
before this date). So I felt I needed an indexed table storing this
information, hence ste_allocation. This then has a many to one
relationship with rows in the other tables (one sales item may involve
several allocations).

I could do this with some kind of indexed view, but the design issue
still remains, I have to be able to determine which entity caused the
allocation of the stock.

They do not currently share the same ID space, but as I am currently
changing their primary keys, they could do. If I do this, it will
markedly increase the gaps in each of their ID sequences, and I was
wondering if I should then be looking at fill factors (the IDs will
always be increasing numerically).

Iain Sharp (iains (AT) pciltd (DOT) co.uk) writes:
It is always difficult to answer database design questions on forums,
you only know so little information. Often database design is quite
difficult even when you have all information, because there are always
trade-offs.

But it seems to me that you should have some supertable for all these
tables, and in that table you would have a type column that details
the type of sale or what it is.

You would then use that column in the allocation table as well.

As for the ID space, you could make create a common space, but you
could also let the supertable have a two column key: (Type, ID),
but it would not work out with an IDENTITY column.

Whichever way you go, the subtables should have an FK to the supertable.
If you have Type in the PK, you need it in the subtable as well. But
in fact, it's a good idea anyway. Here's an idea that I learnt from
Joe Celko:

CREATE TABLE Supertable (ID int NOT NULL,
Type char(1) NOT NULL
CHECK Type IN ('A', 'B', 'C')),
-- More columns
PRIMARY KEY (ID),
UNIQUE (ID, Type))

CREATE TABLE A_Sum (ID int NOT NULL,
Type NOT NULL CHECK (Type = 'A') DEFAULT 'A',
-- more cols
PRIMARY KEY (ID),
FOREIGN KEY (ID, Type)
REFERENCES Supertable(ID, Type)
)

You will find that the solution is not very different from what you have
now with the allocation table, but you no longer have table names in
your data.


Not really sure what you mean here. If you create a common ID space,
you should probably assign IDs as entities comes in. Whether there
is 1, 2, 3, 4 in a table or 1, 6, 12, 13 has no importance, as long
as they are monotonically growing.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

How are they LOGICALLY/PHYSICALLY different? This sounds like a
status change and not a new entity. Consider a carton of eggs and a
skillet. I cannot un-cook an egg nor find it in a omelet. That is the
"different entity", but if I move it from the TRUCK, inspect it and
move it to my PANTRY that is a status change

Yep! The data model is wrong. This is a status attribute and should
not be modeled with tables. You are doing this the way we did it with
punch cards and tapes, not RDBMS.

A reserved status!

If you were older, you would have seen this design flaw in manual
systems that moved items to new physical locations with new physical
location identifiers.

Based on 30+ years in the trade, this is probably more than yoyj can
do in a Newsgroup.

On Tue, 19 Jan 2010 21:15:52 -0800 (PST), --CELKO--
<jcelko212 (AT) earthlink (DOT) net> wrote:

Well, the sales order items table has a price to charge the customer,
a margin being made against cost, and many other fine variables of no
use whatsoever to the production order stock requirements table.
Both of those have a due date field which is of no use to the stock
reservations table.

These are not eggs. These are requirements for eggs. I don't have any
eggs yet. I've got to buy them at some point in the future.

I get an enquiry from a customer, I raise a transaction for that
customer to sell them some material (which may or may not yet exist in
stock). "Sure, I can get you an omlette". I now have a requirement
for an omlette.
I get another, with a new transaction, "Sure, I've got some eggs, how
many do you want? 3? No problem?". I now have a requirement for 3 eggs
for sale.
I decide that I am going to make the omlette. So I create a production
order to make the omlette, which requires 2 eggs. Production orders
are not sales orders, and have different data requirements, so they
are not in the same entity. So I now have a requirement for 2 eggs for
production.
Someone is checking the eggs in the shelves for expiry dates, so they
want to make sure no-one sells the potentially dodgy eggs. So they
lock several physical batches of eggs out from the sales system. I now
cannot sell 24 more eggs.

I have just bought 30 eggs. How many are available for sale/use? How
did you calculate that?

The warehouse want to know which orders to go pick stock for. So I
need the transactions sorted by due date.

Further, the sales order is just for 'some eggs'. The eggs are held as
batches to allow for turnover, so If I want 32 eggs, and they're in 6
egg batches how do you suggest I lock specific batches with a 'status
flag', and having done so, how do I know which transaction locked them

There is a many to many relationship between entered order items and
stock batches. Please model using only a status flag in one of the
tables....


In which bloody entity? Stock? No good, I need it broken down by
transaction. One single transaction table? How do I decide what is
sales and what is production for later?

What? I haven't moved any stock yet. I'm trying to work out how much
needs to move, and when and why.

I'm 42. I've been doing this for 20+ years.

Ian,

as the others said all ideas shot a bit into the blue, but here are
mine:

As far as I understood you have:

Inbound_Orders (your egg purchase)

Outbound_Orders (some action what will cause eggs leave your
inventory) of the types:
- Sales order items (oritem)
- Process order stock requirements (orstkall)
- Picking note items (pickitm)
- Miscellaneous manual stock reservation

Outbound_Order_Details (some additional data based on the specific
type of the order, possibly in different tables which however are all
outbound orders of some kind)

Inventory (an entry is just made, when eggs arrive or go physically
out)

When you need to know the forecast, you will have to create a query
which shows the quantities of all unprocessed orders (and perhaps
their expected execution date)

The idea is to combine your ORITEM, ORSTKALL, PICKITM into the
Outbound_Orders table and add a details table where appropriate.

brgds

Philipp Post