September 21, 2009 at 7:24 am
Hello everybody,
I'm trying to handle various "status"-flags for some of my database tables but can't really seem to figure out a decent way.
I hope I've picked the right category since my problem is kind of a mixed problem 🙂
The thing is that these tables, "Product" for example, should be able to hold more than one StatusFlag or StatusId.
So, let's say the "Product" (it's about renting / lending - function-wise it's like a video store) should get some flag called "Quality checked" and at the same time, a flag called "available". But other combinations should be possible as well or one or even none.
That's why it probably wouldn't make much sense to simply refer to a "Status" table, where I have to write all sorts of combinations.
So I thought about this concept where I have 3 tables but now that I want to write a trigger I can't seem to find a way to handle this.
Example: The customer returns the product and the clerk uses two check-marks in the system to signal that the Product was "checked" and is "available" again. Now the trigger changes the "not available" status back to "available.
Here's what I came up with:
Status
StatusID
StatusName -- "available", "checked",.....
StatusDescription
StatusDescID
StatusDescName -- Tables like "Product", "Customer"....
StatusRelation
StatusID
StatusDescID
StatusObjectID -- should represent the according tables...
Now I don't know whether I'm on the right track here but I hope you can point me in the right direction and if you have a totally different approach then please let me know.
September 21, 2009 at 7:30 am
I'm not sure why you have the status description in there. Does it really matter if a status is linked to a particular table?
Are you saying that if a Customer is "active" and "paid in full" that you'll then have two rows in the Status table for that customer? Or that the Customer table has a "status" column that relates to this Status table?
When you query the customer, then you might end up with two rows coming back.
Typically I've seen systems that needed multiple statuses would model that behavior in the customer table. So customer would have
create table Customer
CustomerID int
CustomerName varchar
Status tinyint -- active/inactive
Balance tinyint -- also a status column
Local tinyint -- also a status
...
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 21, 2009 at 7:49 am
Can you post the code you have for creating the three tables along with a basic description of what the program does? I think that would help us understand the overall view a little better.
September 21, 2009 at 7:52 am
thanks for your quick reply.
hmmm...the thing is that most of these states will be duplicates. For example a customer can be "locked" and a product (for review) too. So I thought why not put all the names for states in one table and with the help of the "StatusDescription" or maybe "StatusTyp" is a better name I then can relate each State with a StatusTyp, which represents a Product, Customer...
Below you write Status, Balance... are these meant to hold only 0 or 1, or do I need extra tables to store their IDs?
create table Customer
CustomerID int
CustomerName varchar
Status tinyint -- active/inactive
Balance tinyint -- also a status column
Local tinyint -- also a status
...
so far that's the code I've created but haven't tested since I don't know what the right thing is.
/* ---------------------------------------------------------------------- */
/* Add table "StatusRelation" */
/* ---------------------------------------------------------------------- */
CREATE TABLE [StatusRelation] (
[StatusTypID] INTEGER NOT NULL,
[StatusID] INTEGER NOT NULL,
[StatusObjektID] INTEGER NOT NULL,
CONSTRAINT [PK_StatusRelation] PRIMARY KEY ([StatusTypID], [StatusID])
)
GO
/* ---------------------------------------------------------------------- */
/* Add table "StatusTyp" */
/* ---------------------------------------------------------------------- */
CREATE TABLE [StatusTyp] (
[StatusTypID] INTEGER NOT NULL,
[Name] VARCHAR(70) NOT NULL,
CONSTRAINT [PK_StatusTyp] PRIMARY KEY ([StatusTypID])
)
GO
/* ---------------------------------------------------------------------- */
/* Add table "Status" */
/* ---------------------------------------------------------------------- */
CREATE TABLE [Status] (
[StatusID] INTEGER NOT NULL,
[Name] VARCHAR(70) NOT NULL,
CONSTRAINT [PK_Status] PRIMARY KEY ([StatusID])
)
GO
/* ---------------------------------------------------------------------- */
/* Foreign key constraints */
/* ---------------------------------------------------------------------- */
ALTER TABLE [StatusRelation] ADD CONSTRAINT [StatusTyp_StatusRelation]
FOREIGN KEY ([StatusTypID]) REFERENCES [StatusTyp] ([StatusTypID])
GO
ALTER TABLE [StatusRelation] ADD CONSTRAINT [Status_StatusRelation]
FOREIGN KEY ([StatusID]) REFERENCES [Status] ([StatusID])
GO
September 21, 2009 at 7:58 am
The thing I think is that does "locked" matter if it's a product or customer?
In my example, I'd still have the status table. I typically have had ID, name, and a description (for help) in the same table. If we needed to have two descriptiosns for locked, we could, though we'd typically change the name as well.
Active might mean different things for Products or Customers, but the status is the same and I don't think you gain anything by putting it in a separate description table. Other than another join and potentially more confusing. At least to me.
You might be more correct from a modeling perspective, but I'm not sure it matters that much. If the difference is low, I look to keep things simple where possible
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 21, 2009 at 8:13 am
To tell you what the database is all about: Customers can borrow tools for a certain amount of time: like chain-saws, power drills and so on.
I want to be able to give out warnings to a customer but also to a supplier.
Then I want that warning to have states like: due, paid, overdue.
I also want to store orders which can be due as well and so on.
That would make at least 2-4 columns for many tables in my db.
And the more I think about it, the more states come to my head 🙂
Just so I understand you correctly. In your Status table you would put all possible states that all required tables could take, right?
And for one table to hold, let's say two states, you'd add a "second" status column.
How do I have to set the foreign keys for those states in a Customer table?
September 21, 2009 at 8:49 am
Active might mean different things for Products or Customers, but the status is the same and I don't think you gain anything by putting it in a separate description table.
I have to disagree with that. You should be constraining any status Ids in your Customer and Product tables to the relevant Status tables.
Putting them in a single table means (database wise) you could set the status of a Product to "Account Suspended" or a Customer to "On Loan" etc.
September 21, 2009 at 8:59 am
That's a good point, Ian, but that depends on how the values are pulled out. The application might constrain the domain of items that are used.
If there need to be limits for status to a particular table, then Ian is correct in that you could end up with some strange occurrences. You might need to build a CustomerStatus, ProductStatus, etc. tables.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 21, 2009 at 9:33 am
so I'll end up with 6-7 Status tables - haven't seen that before 🙂
And do you think my first approach is too difficult or maybe even wrong?
To me it looks more "elegant" (Java thinking) but it might not work at all in the first place.
September 21, 2009 at 9:47 am
I'm not sure it's elegant since you'll end up confusing people that look at the design.
Multiple tables, like Product, ProductDetail, ProductStatus, would form a sub-model I've used that approach as well. It's not in-elegant, IMHO.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 21, 2009 at 10:31 am
so I'll end up with 6-7 Status tables - haven't seen that before
That's not uncommon. The rules of data modelling say "One table, One purpose", so to use one Status table for 2 purposes breaks that rule.
I have seen the opposite, where a single table is used to store all "Reference" data by using a Type code as part of the key... yuk.
If the various statuses your Product could have are distinct (not sure if that's the right word here), then leave the status codes in your Product table.
For example, if your Product can have a status of "Locked" or "Unlocked", then have a "Locked" flag in the Product table, (referencing a LockStatus table if you wish). If the Product can be "Active" or "Inactive", then also have an Active flag in the Product table etc etc.
If you were to adopt a many to many link (i.e. Product ==> ProductStatus <== ProductStatusDescription), then what's to stop you adding ProductStatus entries that show a Product as both "Locked" and "Unlocked". And don't say "the application will make sure", because that can be broken by a bug in the application, or the DBA running a script to do some "tidying up".
If you model the database properly, and put the constraints where they should be, it will save a lot of grief later on.
September 21, 2009 at 4:18 pm
"status" indicates a result of actions taken on an entity, or lack of any action.
Every time you query for results of different actions - you name it different statuses.
So, ditch the statuses and create a log of actions for the entity.
Then you'll be able to find out the answer on any question about current status just reading the "log" table.
_____________
Code for TallyGenerator
September 22, 2009 at 1:37 am
Sergiy (9/21/2009)
"status" indicates a result of actions taken on an entity, or lack of any action.Every time you query for results of different actions - you name it different statuses.
So, ditch the statuses and create a log of actions for the entity.
Then you'll be able to find out the answer on any question about current status just reading the "log" table.
that sounds interesting - but I have to admit I haven't done that before.
Do I need only one log table or every entity gets it's own?
And can one entity take more than one action at the same time?
Right know I don't really know how the structure of the whole thing would look like. Even after reading this for example: http://qa.sqlservercentral.com/scripts/Miscellaneous/31979/
Would you be so kind and guide me what needs to be done? Than I could try to model it in MS Visio and let you have a look at it.
thanks in advance
EDIT: here's a link I found to "my" approach: http://qa.sqlservercentral.com/articles/Basics/lookuptabledesign/1424/
September 22, 2009 at 6:07 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply