#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    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.

    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    120
    Rep Power
    10
    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.)
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo