Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tonyclifton
Starting Member
12 Posts |
 Posted - 2009-09-22 : 13:37:24
|
Hello to all,I don't know how I can handle multiple statuses for an entity. An entity ("Product") might have a status "checked" AND a status "available" or it might only have "locked" as a status.I posted this in TSQL because at the end I want to be able to have a trigger or whatever gets the job done, to update those statuses of an entity by clicking a button in the application. I have about 7 entities which will need to handle multiple statuses, so I believe that creating a relation-entity for each entity might not do the trick very well.Instead I thought of a structure with three entities that handle statuses.Status (which holds the names like "locked", "checked"), StatusType (which holds the various entities) and a StatusRelation 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 StatusRelation entity, which holds the Object, could that do the trick, or should I say good-bye to the whole approach  I hope you guys have an answer to this - google couldn't really point me in the right direction  Thanks for reading. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-09-22 : 13:47:23
|
| There are several ways to handle this, depending on your business rules.1. Make a one-to-many relationship using 3 tables. Something like Product, ProductStatus and Status tables. So a particular product can have mutiple tatuses.2. You can use a bit mask to store mutiple statuses for a single Product using a single INT column. |
 |
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-22 : 13:49:12
|
| 3 table option is how it is handled here. Hope I can explain.In your case you have:Product Tablewith Product_IDThen you would have:Status Tablewith Status_IDThen you would have:Product_Status Tablewith Product_IDand Status_IDProduct Table to Product_Status Table is 1 to many(or 1)Procuct_Status Table to Status Table is 1 to 1We use the same scenario to get Team Leader to Team Members.John"The smoke monster is just the Others doing barbecue" |
|
|
|
tonyclifton
Starting Member
12 Posts |
Posted - 2009-09-22 : 14:06:02
|
| so the easiest way is to have an "Entity_Status" for each required entity? In my case, I'd need 7 of these.Is "my" approach somewhat useful (I tend to think in objects - Java :-) or will this lead to difficult queries / triggers etc. ? |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-22 : 14:13:25
|
| Yes, but I think it greatly simplifies our queries. Based on info found here I created a function that will return a list of Team Members when given a Team Leader so we reduce the number of joins in our SQL. You could have same that returns products in a list based on status. It took me a few queries to wrap my head around it but love it now. I will admit though that everywhere we use that technique, the data does not change much after getting originally written where as it sounds like your might change often.John"The smoke monster is just the Others doing barbecue" |
|
|
|
tonyclifton
Starting Member
12 Posts |
Posted - 2009-09-23 : 03:35:16
|
| thanks for the info! Could you please give me an example on how a trigger is used to update the StatusID from one entity to another? For example, a "Product" gets its status changed by an "Order". |
|
|
|
huntera
Starting Member
4 Posts |
Posted - 2009-09-23 : 04:32:05
|
| Hi Tony,Ever since using Microsoft Dynamics CRM, I have copied their "Status" architecture as think it's pretty nifty:Each entity (Product, Account etc) has 2 columns, StateCode and StatusCode. The StateCode would be something like "Active" or "Inactive" and StatusCode would be a sub-division of the State, "Open" or "Cancelled" or something else.The StateCode and StatusCode are stored as integers in the entity tables, which link to a "StatusMap" table.The main logic happens in the StatusMap table (a Matrix), and defines the Many-Many relationship. In this table you have an EntityCode (like product / account) the StateCode, StatusCode, and a flag for whether the state is default or not.Microsoft go one step further here and have a further table "StringMap" which acts as the lookup for the State And Status from the StatusMap table.A good bit of waffle there. Not sure if it'll help, but thought I'd give you my idea. The good news is, there is loads of info on the Microsoft CRM table structure only a google away. (In fact I think you may be able to get a copy of the db for trial, then you can take the idea!)Cheers. |
|
|
|
|
|
|
|
|
how to handle more than one status for an entity?