Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2009
    Posts
    8

    Question Unanswered: how to manage more than one status for an entity?

    Good morning to all,

    I'm a bit stuck with my database model concerning the handling of statuses and I couldn't find an answer so far.

    What I want to achieve is for an entity to be able to handle more than one status. So that later I could create a trigger which updates the status of a product activated by a certain action.

    For example a "Product" entity should be able to take a status of "checked" and also "available" but of course "checked" could also be the only status for the product, so I can't just have a status called "checked and available", since different functions should be allowed to change only one status as well.

    I hope I could describe it well enough how I want it to work.

    I got the suggestion that I should create a Status entity and a ProductStatus entity but that would mean I'd have to create 7-8 of these relations for all required entities.

    So I thought about a different way but I can't figure out if this would work after all.
    Here it is:

    Status (which holds the names like "locked", "checked"), StatusType (which holds the various entities, like product) and a StatusInfo that takes the FKs of StatusID and StatusTypeID.
    Now that way I can't handle more than one status (at the same time) - at least I don't see how.
    If I add another ID to the StatusInfo entity, which holds the Object (Product), could that do the trick, or should I say good-bye to the whole approach?

    I have a function for "getStatus", "getStatusType" and one to get the Object plus StatusId.

    Code:
    CREATE FUNCTION [getStatusObject](@oid int, @stid int) RETURNS varchar(200) AS
    BEGIN
    	DECLARE @ret	varchar(200)
    	DECLARE @sid	int
    	
    	SET @ret = ''
    	
    	DECLARE mycursor CURSOR FOR SELECT StatusID 
    FROM StatusInfo 
    WHERE StatusObjektID = @oid AND StatusTypID = @stid
    	OPEN mycursor
    	
    	FETCH NEXT FROM mycursor INTO @sid
    
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		SELECT @ret = @ret + Name + ', ' 
    FROM [Status] 
    WHERE StatusID = @sid
    	
    		FETCH NEXT FROM mycursor INTO @sid
    	END
    	
    	CLOSE mycursor
    	DEALLOCATE mycursor
    Would this work well? If yes would you be so kind and help me to find a solution for a trigger that will update a certain status?

    Thank you for reading, I hope for some ideas.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Tony
    Quote Originally Posted by tonyclifton
    I got the suggestion that I should create a Status entity and a ProductStatus entity but that would mean I'd have to create 7-8 of these relations for all required entities.
    ^^This^^. You don't need a Status table unless you need to store information about Statuses (e.g. descriptions). Why worry about creating more tables?

    Quote Originally Posted by tonyclifton
    So I thought about a different way but I can't figure out if this would work after all.
    Here it is:

    Status (which holds the names like "locked", "checked"), StatusType (which holds the various entities, like product) and a StatusInfo that takes the FKs of StatusID and StatusTypeID.
    Now that way I can't handle more than one status (at the same time) - at least I don't see how.
    If I add another ID to the StatusInfo entity, which holds the Object (Product), could that do the trick, or should I say good-bye to the whole approach?
    Sounds a bit like a OTLT (or one true look up table). Try googling this term.
    EDIT - a link to something more like what you describe http://www.sqlteam.com/article/imple...ble-interfaces. Note that Jeff's "Table inheritance" article linked to on there is actually Jeff's term for the sub type super type referred below (he's writting these articles in a sort of OO perspective).

    Otherwise, it might be that all your applicable entities (we know one is called "product") are inheriting from an undefined overarching entity, in which case you should investigate sub types and super types.

    I don't understand your function - why return a CSV of IDs?
    Last edited by pootle flump; 09-23-09 at 06:18.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to clarify my first point: "how to manage more than one status for an entity?" You say it yourself - this is a one to many relationship and should be modelled like any other. Less tables is not a goal in database design.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2009
    Posts
    8
    ^^This^^. You don't need a Status table unless you need to store information about Statuses (e.g. descriptions). Why worry about creating more tables?
    Well the product should display the statusName = "available" in the application, for example.

    Many of the status names are the same for other entities.

    Just to clarify my first point: "how to manage more than one status for an entity?" You say it yourself - this is a one to many relationship and should be modelled like any other. Less tables is not a goal in database design.
    So it is: Product - ProductStatus - Status, where ProductStatus takes ProductID and StatusID, right?

    Let me make another example: A customer can borrow a product, so the product gets a status of "unavailable" and when he returns it, an Employee uses a trigger which shall update the "unavailable" of the product back to "available" and also marks the product as "un-checked" for later review.

    If the customer won't return the product in time, I want the customerStatus get a "warning" or "over-due" and also be able to later "lock" the customer by his status.

    How can I make that happen?

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by tonyclifton
    So it is: Product - ProductStatus - Status, where ProductStatus takes ProductID and StatusID, right?
    That seems reasonable to me.

    Quote Originally Posted by tonyclifton
    Let me make another example: A customer can borrow a product, so the product gets a status of "unavailable" and when he returns it, an Employee uses a trigger which shall update the "unavailable" of the product back to "available" and also marks the product as "un-checked" for later review.

    If the customer won't return the product in time, I want the customerStatus get a "warning" or "over-due" and also be able to later "lock" the customer by his status.

    How can I make that happen?
    This seems like the same example as before so why not use the same solution? Maybe I don't know what you are asking. Is this really a design question or are you asking how to write a stored procedure?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tonyclifton
    Well the product should display the statusName = "available" in the application, for example.
    Yes, but you don't need another table for this.
    products (pid:int)
    product_statuses (pid:int, status:varchar)
    Check constraint on status. You don't have to do it this way - just pointing out you don't have to use a status table - there are pros and cons to both.

    Quote Originally Posted by tonyclifton
    Let me make another example: A customer can borrow a product, so the product gets a status of "unavailable" and when he returns it, an Employee uses a trigger which shall update the "unavailable" of the product back to "available" and also marks the product as "un-checked" for later review.

    If the customer won't return the product in time, I want the customerStatus get a "warning" or "over-due" and also be able to later "lock" the customer by his status.

    How can I make that happen?
    I would say you are overcomplicating things. Why have triggers updating columns in table A based on values in table B? Why not have the application act on the values in table B? KISS.
    Last edited by pootle flump; 09-23-09 at 07:24.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2009
    Posts
    8
    ok, the thing with the updating is making it complicated for me now.

    So you suggest that I should use a sp to simply update the statusId of a product and leave triggers out of it? Won't I benefit from triggers here?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No. I am saying perhaps forget status.

    I am not totally clear on what your process is but it seems that you are saying if a book is checked out (and recorded as such in another table) its status should be changed to unavailable. I am saying, why bother with status? Instead derive the status at run time by looking at whether or not it is checked out in the other table.

    It is not absolutely always the case, but 99 times out of 100 you should not store anything you cannot derive.
    The 1 case in 100 is usually applied to things that will not change, or things that are incredibly costly to derive so should not be derived at run time. Neither of these appply here.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2009
    Posts
    8
    but if I forget about status I can't check for certain criteria. Let's stay with the book example.
    So I'd have a book table - I want to indicate that the book is "available" or "un-available" (a customer took it) but it could also take another status.

    I'd also have a table to store the "lending" process so I record the bookID and the customerID ... in there. Here I need to indicate that the "lending" is still taking place or that the book was returned.

    And once the status in the "lending" table changes to "returned" the status in the book table could then automatically change to "available" again.

    How else would I manage that without the help of one or more status for both tables?
    Last edited by tonyclifton; 09-23-09 at 08:40.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I understand the difference between available and unavailable, but I don't understand the difference between lending and unavailable...
    George
    Home | Blog

  11. #11
    Join Date
    Sep 2009
    Posts
    8
    Quote Originally Posted by gvee
    I understand the difference between available and unavailable, but I don't understand the difference between lending and unavailable...
    with "lending" I just mean the name for a table. And that table can have a status called "borrowed" (whatever the right word is . This has the advantage, I think, that within a search you can check how many books haven't been returned and as long the book is still borrowed it can't be made available in the book table.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You are saying then that a book would unavailable when it is lent out, but also it could be unavailable for other reasons?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    If the availability is determined by the status, an easy fix would be to store the Availability in an extra column of the Status table:
    Code:
    Id  Name     FlagAvailable
    1   borrow   N
    2   return   Y
    3   inRepair N
    4   ordered  N
    .....
    But you may consider to rethink this part of the design. As others have stated before, don't store information you can derive. It means redundancy. And redundancy means headache over time.

    How about storing the "situation" of a product, together with a start and end DATETIME? Something in the line of (simplified):
    Code:
    Product DTStart     DTEnd       WhoHasIt   Event
    BookA   2009.06.01  2009.06.11  Publisher  ordered
    BookA   2009.06.12  2009.06.12  Library    intake
    BookA   2009.06.13  2009.06.17  Library    inLib
    BookA   2009.06.17  2009.06.18  Library    inRepair
    BookA   2009.06.19  2009.06.25  Library    inLib
    BookA   2009.06.26  9999.12.31  John Doe   lendOut
    You could query the system of the availability of bookA with
    Code:
    SELECT 'It is available!'
    FROM MyTable
    WHERE GetDate() >= DTStart AND GetDate() < DTEnd
    	AND Event IN ('InLib', 'onShelf')
    	AND Product = 'BookA'
    It means you would have to write one transaction where you both
    - "end" the previous situation: UPDATE the DTEnd of the "previous" record (the one with DTEnd = 9999.31.12) and SET it to GetDate()
    - "start" an new situation: INSERT a new record with DTStart = GetDate() and DTEnd = 9999.31.12

    You could do it without the DTEnd column. But then you would always have to select the MAX(DTStart) for a certain Product to know what its current status is. It complicates all SELECTS and it could turn out to be expensive at run time.

    Just my 5 cnts.

    Edit:
    I think, that within a search you can check how many books haven't been returned
    Code:
    SELECT COUNT(*)
    FROM MyTable
    WHERE GetDate() >= DTStart AND GetDate() < DTEnd
    	AND Event = 'lendOut'
    Last edited by Wim; 09-23-09 at 10:50.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  14. #14
    Join Date
    Sep 2009
    Posts
    8
    thanks for the ideas.I will have to let that sink in.

    How about if I just use 2 extra columns for the Status - one for determinating "available" and "unavailabe" and the second status precises the first status?

    Like this: "Book available -> in Store"
    or "Book available -> with Customer"
    and so on.

  15. #15
    Join Date
    Sep 2009
    Posts
    8
    sorry to bother you again.

    Right now I'm trying to solve my first approach (from my first post) with those 3 tables.

    All the updates / inserts that a book, customer and so on... take would be put in that "StatusInfo" table.

    So I have 3 columns:
    Code:
    StatusID
    StatusTypeID
    StatusObjectID
    If I wrote a trigger or stored procedure that automatically updates the book object after a customer has returned the book (table "lending") - could I distinguish between those objects in that one table?
    The book and "lending" table both have IDs starting with 1.

    So I need the StatusType to sort that out - but how? Could you please give me an example how that could work?

    Thanks again!
    Last edited by tonyclifton; 09-24-09 at 08:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •