SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table for Object with Multiple States


Table for Object with Multiple States

Author
Message
rootfixxxer
rootfixxxer
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 532
Hello

I'm facing a problem with a table inside a database that i created.
When i designed the table, the object only had four states, like this, created, analyzed, ready and dispatched. For each one of them i save some common properties for example, start date, user, end date, user that ended, but there are some properties that are specific for some state...
This table has more than 100 fields, in the beginning i thought that the design was good, but in the last weeks the requirements changed, and now i have a new state between created and analyzed, named validated.

Now i have to redesign the table, but i don't think the way i did the things it's the best way (to solve this problem i just needed to add some more columns), so i was thinking if anyone can give tips how to handle this kind of situation?

The solution that i'm looking for should let me in the future add or remove the number of states for the object without changing the database schema.

The solution that i can think of, it's using some kind of state table related to the object table where i have the common properties, and in the object table i have some reference to the number of states and which states... but how i handle the not common properties?

Thanks
rootfixxxer
rootfixxxer
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 532
No one... Sad
LutzM
LutzM
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12789 Visits: 13559
rootfixxxer (12/23/2010)
No one... Sad

One reason for not getting an answer yet might be a missing table structure (reduced to the relevant columns) with some sample data so we can visualize the design.
For some of us the reference to an "object" (like "...the object only had four states..") probably is more confusing than helpful.

But you're right, we could have asked for clarification. And herewith that's what I do: please clarify. ;-)



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
rootfixxxer
rootfixxxer
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 532
Sorry...
Like i said the original table has more than 100 columns, so i'll put here a sample of one similar with less columns.

Let's say that the company manufactures products for sale, when i say manufacture i say all the stages. Start in the design (2D design department), approve the design (another department), modeling (3D department), send to factory (factory department).

For each department i have to have some common properties, the accepted bit, rejected bit, accepted date, rejected date, accepted by, rejected by, number of rejections, expected send date (to another department), who sent, sent bit.

Now some differences, from the 2D design to approve the users should add some drawings (paths), from modeling to factory the user must indicate the time that the product will take to be manufactured, in the factory the users that work in the product must write how much time it takes for each operation (cutting, soldering, etc.).

Product Table (simplified version)
id
Name
HasDrawings (a related table by the id)
UserSentToApprove
DateSentToApprove
---- Common to the Sections ---
Accepted
Rejected
AcceptedBy
AcceptedDate
RejectedBy
RejectedDate
NumRejections
Sent
ExpectedSendDate
SendDate
------ I have the above columns duplicated for evey section with different names ---
---- Modeling Section ----
TimeToDelivery
---- Factory Section ----
TimeToCut
TimeToSolder
TimeToAssemble
TimeToWhatever
----- Some General ---
Finished
Discarded
Removed

I have more columns, but for the purpose of explain my problem, i think this's enough. :-)

Thanks
LutzM
LutzM
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12789 Visits: 13559
I think the table design needs to be re-evaluated...

Basically there are products, sections and status history per product and section. There might be section groups as well (at least what it looks like, e.g. Design, Modeling and Factory).
I don't know anything about the business concept, therefore let me ask: Did at least one product got manufactured based on two separate orders a few weeks/month apart?
If so, was the same or a new productid used for it?
See what I mean? Design and production don't belong into the same table, since the first happens only once per product (hopefully) but the production should frequently run the product (hopefully, again).
I strongly recommend to normalize the table structure since it'll help you big time in the future.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
rootfixxxer
rootfixxxer
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 532
Sorry for the late response... holidays Smile

Yes i know that i need to redesign the table, that's why i ask for help Smile

My business logic isn't like the normal product logic, every product it's different from the others, so it has to have it own reg.
Like i wrote, i have 4 transitions and 6 states for each one, the product it's created (process/docs), the process it's delivered to the 2D design, sent to approval, sent to 3D, sent to production and finally the product it's finished.
I need to save all the dates and the users that do something in all the process...

I do understand that i need to somehow break this big table, into small ones, but that's my problem i don't know what's the best way to do that, and do that in some kind of way that left some room to change the number of states that the products can have in the future (my boss may want to add for example, another approval between the 3D and the production or remove the existing one between the 2D and the 3D department).

Thanks
Toby Harman
Toby Harman
SSC Eights!
SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)

Group: General Forum Members
Points: 801 Visits: 668
OK - From what I've understood.

Each Status has a set of common columns, which might include who changed it to that status, when the change happened etc.

These common columns are the same for all the status changes.

So what I would do is to capture is the details of a status change for the object.

So separate those out into a separate table along the lines of

Table ProductStatusChange
ProductId (FK to Product)
StatusType (Either a foreign key to a validation table of acceptable Statuses or the actual Status)
ChangeDate (Date the status was changed to the value above)
Accepted
Rejected
AcceptedBy
AcceptedDate
RejectedBy
RejectedDate
NumRejections
Sent
ExpectedSendDate
SendDate

That will shrink your table some and allow addition of new Statuses.

Things like TimeToCut are actually attributes of the product and should remain keyed by the product.

If there are a significant number of columns that are not frequently accessed then I would consider splitting the Product table into Product and ProductExtension, but only if this would either simplify my life (big tables are a pain) or cut down on I/O (huge text blocks that aren't used much).
Evil Kraig F
Evil Kraig F
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10651 Visits: 7660
Hey Root,

What Toby said above looks right at a glance, but I believe the problem is more basic for you. You've misunderstood, or haven't been informed, of some basic principals of database design.

You want to look into 'normalization'. There are about a billion articles on the web, but in essence you're repeating data. In specific look up information on 1st normal form, 2nd normal form, and 3rd normal form. You want to aim for 3rd normal in most circumstances unless you have specific reasons to avoid it.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
rootfixxxer
rootfixxxer
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 532
Thanks for the replys

@Toby Harman
I was thinking in something like that, but i must check some things before doing that.
My application main core works around this table, i have to do several query's against it, and that kind of schema, will increase the complexity...

@Craig Farrell
I know about normalization and the normal forms, but when i was developing the application and designing the database, kind of "on-the-fly", learning the business rules and developing/designing at the same time, i had to make some choices, and like i wrote above, to decrease the complexity of the querys i choose to put all into a big table, with all properties related to the product itself...

One of the queries that i need to do, it's to display the forecast of the load of the factory, in this query i need to check every product that isn't sent to factory, and check the state.
If it's created/defined i have some internal rules (predefined) that tells the max time that it can be in each department.
If it's in 2d modeling department, that department when accepts has to define when it will be ready to send to the next department and what department, and after that date i use the predefined values.
If it's in approval once again the department defines when it's ready, and the same above rule it's applied... and so on...

And one more thing, i forgot to tell, the product can skip some states, for example it can be sent from the 2D to 3D without passing for approval, or directly from 2D to the factory, and i need to handle this situations, in the actual table i just set the properties for the skipped state with default values, but once again i don't like this approach...

If all this info is in the same table it's a lot more easier to create the query...

So i need to come up with a solution that do all this, without making my query's extremely complicated and more or less flexible to increase or decrease the number of departments/states that the product can pass/have in the future.

Thanks
LutzM
LutzM
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12789 Visits: 13559
If all this info is in the same table it's a lot more easier to create the query...

So i need to come up with a solution that do all this, without making my query's extremely complicated and more or less flexible to increase or decrease the number of departments/states that the product can pass/have in the future.

Don't get me wrong, but isn't that a contradiction all by itself?

I guees I have to disagree that a concept of "all in one table" by purposely violating the basic normalization rules will help you in the long run.

I strongly recommend to normalize your tables. The sooner the better. Normalization is the key for flexibility, I'd say.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search