November 22nd, 2012, 10:56 PM
Dynamic column view / groups best practise
Hope I've explained myself well enough :)
Thanks for reading.
Branch table (BranchID, Name)
Location table (LocationID, Name, LocationTypeID, BranchID)
Item table (ItemID, ItemName)
Stock table (StockID, ItemID, LocationID)
Basically I want to create location groups (LG), each LG with their own name and set of locations.
Then I want a view that lists the items in the item table with a column for each LG that displays a count of items in the stock table that have a LocationID part of the group.
In addition I would like the flexibility of defining an LG dynamically by such things as BranchID coupled with LocationTypeID.
The way I thought of doing Location Groups to allow for flexibility is as follows:
LocationGroup table (LocationGroupID, Name)
LocationGroupEntry table (LocationGroupEntryID, LocationGroupID, BranchID, LocationTypeID, LocationID)
And the logic I am using for getting the LocationIDs described by a LocationGroupEntryRow is SELECT LocationID FROM Locations WHERE (BranchID = LGER.BranchID AND LocationTypeID = LGER.LocationTypeID) OR LocationID = LGER.LocationID
Hence, each row in LocationGroupEntry allows you to define locations in one of two ways: by entering a branch id with location type id, in this case the location id column would be entered as null; or by setting LocationID and putting null for both branchid and locationtypeid.
My issue comes next... How to go about creating a view that lists each item in the Item table, coupled with columns for the total count of that item in the stock table for each location group.
November 23rd, 2012, 08:44 AM
If your question just about view with calculations I would not recommend that, because with small amount of data it will work, but as you collect more data it will become slow. I think you should create stored procedure which will produce that result for some range of data (for location, or group and etc.)
November 25th, 2012, 07:42 PM
hi gk53... thanks for your reply...
I want a view of the items, with several totals next to each item -- each of these totals being a count of that item in a group.
I'll have a look into stored procedures.