|
|
Posted by John Bell on 06/02/05 17:33
Hi
You may want to make the columns NOT NULL with a zero default and a FK for
your ProductCode (which seems quite long!).
John
"Axel" <axelg@gofree.indigo.ie> wrote in message
news:1117720831.301982.257100@z14g2000cwz.googlegroups.com...
> David,
>
> many thanks for clearing this up. I have went for the computed field
> this is the stored procedure that changes the underlying table
> (snipped):
>
> <snip>
>
> ALTER TABLE dbo.tblStock
> DROP CONSTRAINT DF_tblStock_SunOrder
> GO
> CREATE TABLE dbo.Tmp_tblStock
> (
> YearWeek int NOT NULL,
> ProductCode nvarchar(50) NOT NULL,
> StockonHand int NULL,
> StockonOrder int NULL,
> TotalOrder AS
> MonOrder+TueOrder+WedOrder+ThursOrder+FriOrder+SatOrder+SunOrder,
> MonOrder int NULL,
> TueOrder int NULL,
> WedOrder int NULL,
> ThursOrder int NULL,
> FriOrder int NULL,
> SatOrder int NULL,
> SunOrder int NULL,
> Dummy1 nvarchar(50) NULL
> ) ON [PRIMARY]
> GO
> <snip>
> the reason I can not change the design so easily (as much as I would
> like to) is that it is currenty used in at least 22 queries and 11
> forms in the frontend. It is used during Stock upload (importing a raw
> table which is normalized in the same way) and stock reporting and in
> all cases the separate storage of days in week fields seems to be quite
> intuitive and performance appears to work quite well, so I am not too
> bothered about changing it any time soon. Apart from that the data of
> this table is not used anywhere else so this is more like a view
> anyway. I am quite glad though that I did not create a trigger as it
> would not work on row level anyway and would decrease performance.
>
> Is your suggestion of Normalization based on 3rd NF?
>
> regards
> Axel
>
Navigation:
[Reply to this message]
|