Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Table for Object with Multiple States Expand / Collapse
Author
Message
Posted Thursday, December 30, 2010 5:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:44 AM
Points: 110, Visits: 450
@LutzM
Until the process change, i think that all in one table product case was a good choice, and if my company doesn't had changed the process I never had thought in redesign...
I think that's easier to make a:
SELECT * FROM Table 

than:
SELECT * FROM Table1 INNER JOIN Table2 ON ... INNER JOIN Table 3 ... 

But now the process has changed and i need to adapt to apply the changes, so i need to go through the normalization process, but are some doubts, btw that's the point of this thread, that i need to clarify to make a good and flexible schema this time...

For example I don't see how i would design the tables to make the query to handle the situation that i described in my last reply, the skipping part of departments, the resulting JOINS, and some other issues that i don't recall right now...
Post #1040821
Posted Thursday, December 30, 2010 5:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:47 AM
Points: 6,842, Visits: 13,368
I don't think you'll get the help you're looking for in a forum.
All we can do is point you in a direction or help you with a specific question.
But to come up with a normalized table schema without a detailed description of the business logic is unlike to be successful. I mentioned a basic example of a data structure in one of my previous posts.

On the other hand I think you're too much focused on the current status. To design a table structure the "big picture" is important. For instance, it doesn't matter that there are "4 transitions and 6 states for each one". All that matters is: there are transitions and states for each one. Transitions can be skipped as well as states within a transition.

The same applies to the process flow. Currently, you don't have the scenario where a product will go into production based on an earlier design. But is there anything written in stone with blood by your management that this will never ever happen? Even if: why not to design it in a way where you can say "No problem, already included in the database design?"

I think a good Business Analyst would be able to help you with your table design much more efficiently than a forum can do.




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
Post #1040823
Posted Thursday, December 30, 2010 7:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:44 AM
Points: 110, Visits: 450
I have to look in the forums, at least for tips, for several reasons, i have to solve this problem, i have to solve it by myself because I'm the only programmer here and i don't know any business analyst.

Anyway, i'll try to write down every property possible for the products table, then i will through normalization, and see what i get in the end...

Post #1040903
Posted Thursday, December 30, 2010 10:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
LutzM (12/30/2010)
[quote]I strongly recommend to normalize your tables. The sooner the better. Normalization is the key for flexibility, I'd say.


Building on Lutz's comment I'll say you cannot pull a database design out of a hat - proper data modeling is the foundation of a sound database design.

Do things the right way, start with an ER Model that truthfully represents business reality and needs then move into a logical model and normalize to the 3NF - Once validated then build your tables.

Notes:
ER Model - Entity/Relationship Model
3NF - Third Normal Form


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1041047
Posted Thursday, December 30, 2010 3:26 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:03 PM
Points: 405, Visits: 572
Maybe we are all trying to solve the wrong problem.

What are you trying to achieve?

More flexibility and the ability to change the workflow more easily?
A reduction in the number of columns in your table?

By the way - I would keep the current status in the Product table. That is an attribute of the product. That may help with some of the complex queries.

Ultimately, while I am a great fan of the KISS principle, the reason that we get paid the princely salaries (ha!) is that we can do the complex queries and make them work. Sometimes life is complex, so the model has to be capable of dealing with it. Complex queries are not inherently bad. Over-complicated queries are!
Post #1041234
Posted Friday, December 31, 2010 2:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:44 AM
Points: 110, Visits: 450
@Toby Harman
More flexibility and the ability to change the workflow more easily?
- True
Just to be capable of handling future changes in the workflow, like this one that I'm dealing now, and don't need to redesign the tables...

A reduction in the number of columns in your table?

Not the main goal, but has to be reduced by the normalization. Like i wrote before, i put all in the same table because i was learning the business process and developing at the same time, so the table was built incrementally without any thoughts in the possibility of workflow changes, but now i have to change this. I could easily add some more columns to handle the new department, but if in the future the process changes by reducing the number of departments then i can't just remove the columns...

we get paid the princely salaries

Not here, i must do the job of a bunch people (analyst,programmer, database architect, database administrator, tester), and only get one poor salary...

About the queries, my only problem it's to put the hands again, in some complex and complicated queries that i had created...
Post #1041329
Posted Monday, January 3, 2011 2:58 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:03 PM
Points: 405, Visits: 572

Just to be capable of handling future changes in the workflow, like this one that I'm dealing now, and don't need to redesign the tables...

I'm sorry to say that I believe it is necessary to re-work these tables to allow this.

Post #1042080
Posted Tuesday, August 9, 2011 2:57 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:31 PM
Points: 150, Visits: 954
Its a great question. It reminds me of a human stem cell that can become one of about 250 specialized cells in the body. There are design patterns for this in OOP languages but DBAs tend to stick to Codd's rules of normalization.

I've often felt there was a trade off of creating specific tables for each state if the data requirements were radically different for each processing state. That would also simplify the state change rules in your procedures by spreading them out over many state specific procs. In most of my projects there is only a stored procedure "interface" so the actual tables are always hidden inside the "object" and implementation won't matter for the programmers.

On the other hand, if you use different tables for each state, many SELECT procs may need to be updated to handle each new state table that is added. You'll end up with lots of joins and unions.

I prefer NOT NULL foreign key columns so as to eliminate OUTER JOINS so I lean towards the one table per state implementation but only if your rules are so complex as to warrant treating the 'object' as a separate table/entity.

You may create composite views on subsets of states to handle the retrieval problems. You may also break off the varying sets of columns per state and create child tables to handle them (just to avoid numerous columns being null and technically not available per some states).
Post #1157285
Posted Friday, August 12, 2011 12:14 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
Bill Talada (8/9/2011)
... but DBAs tend to stick to Codd's rules of normalization.


May be this is because Codd put together the basics of the Relational Theory and we are dealing with Relational Databases (RDBMS)

By the way, we DBAs also tend to use normalization only for OLTP systems while rely mostly in Dimensional Modeling for DSS like Data Warehousing.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1159452
Posted Friday, August 12, 2011 1:10 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:31 PM
Points: 150, Visits: 954
No one has really solved the "impedance mismatch" between developers using Objects in their code and DBAs using Relational tables. I've seen some implementations where just primary keys and sort keys are stored as columns while all other object attributes get stored in one big XML column. XML is elegant in how it can adapt to new schema versions in a backward compatible way. It also allows for tree structured data that is more natural than the CODD parent/child table setup that we are used to. We add a lot of unnecessary querying for tree structured data that applies only to one row in a table, storing child/branch data in many tables that will never be reported on from lower levels.

But, of course, a pure Object Oriented Database has the opposite problem of slow retrieval due to search items being difficult to access; one must instantiate the object to pull out the proper attribute just to see if the row meets the selection criteria.

Whoever solves this mismatch problem will become a very rich person some day.
Post #1159487
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse