InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 22:29:13 GMT20http://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxNo 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.Fri, 12 Aug 2011 13:10:47 GMTBill Taladahttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspx[quote][b]Bill Talada (8/9/2011)[/b][hr]... but DBAs tend to stick to Codd's rules of normalization.[/quote]May be this is because Codd put together the basics of the Relational Theory and we are dealing with Relational Databases ([b]R[/b]DBMS) :-DBy the way, we DBAs also tend to use normalization only for OLTP systems while rely mostly in Dimensional Modeling for DSS like Data Warehousing.Fri, 12 Aug 2011 12:14:19 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxIts 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).Tue, 09 Aug 2011 14:57:08 GMTBill Taladahttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspx[quote]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...[/quote]I'm sorry to say that I believe it is necessary to re-work these tables to allow this.Mon, 03 Jan 2011 14:58:20 GMTToby Harmanhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspx@Toby Harman[quote]More flexibility and the ability to change the workflow more easily?[/quote] - TrueJust 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...[quote]A reduction in the number of columns in your table? [/quote]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...[quote]we get paid the princely salaries[/quote] Not here, i must do the job of a bunch people (analyst,programmer, database architect, database administrator, tester), and only get one poor salary... :ermm:About the queries, my only problem it's to put the hands again, in some complex and complicated queries that i had created... :-PFri, 31 Dec 2010 02:55:26 GMTrootfixxxerhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxMaybe 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!:hehe:) 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!Thu, 30 Dec 2010 15:26:33 GMTToby Harmanhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspx[quote][b]LutzM (12/30/2010)[/b][hr][quote]I strongly recommend to normalize your tables. The sooner the better. Normalization is the key for flexibility, I'd say.[/quote]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 Model3NF - Third Normal FormThu, 30 Dec 2010 10:52:34 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxI 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... ;-)Thu, 30 Dec 2010 07:44:33 GMTrootfixxxerhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxI 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.Thu, 30 Dec 2010 05:24:47 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspx@LutzMUntil 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:[code="sql"]SELECT * FROM Table [/code]than:[code="sql"]SELECT * FROM Table1 INNER JOIN Table2 ON ... INNER JOIN Table 3 ... [/code]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...Thu, 30 Dec 2010 05:04:43 GMTrootfixxxerhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspx[quote]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.[/quote]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.Thu, 30 Dec 2010 04:14:29 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxThanks for the replys@Toby HarmanI 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 FarrellI 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.ThanksThu, 30 Dec 2010 04:07:23 GMTrootfixxxerhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxHey 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.Wed, 29 Dec 2010 17:27:10 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxOK - 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 ofTable ProductStatusChangeProductId (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)AcceptedRejectedAcceptedByAcceptedDateRejectedByRejectedDateNumRejectionsSentExpectedSendDateSendDateThat 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).Wed, 29 Dec 2010 16:57:34 GMTToby Harmanhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxSorry for the late response... holidays :)Yes i know that i need to redesign the table, that's why i ask for help :)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).ThanksTue, 28 Dec 2010 07:36:14 GMTrootfixxxerhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxI 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.Thu, 23 Dec 2010 07:57:14 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxSorry...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.).[b]Product Table (simplified version)[/b]idNameHasDrawings (a related table by the id)UserSentToApproveDateSentToApprove---- Common to the Sections ---AcceptedRejectedAcceptedByAcceptedDateRejectedByRejectedDateNumRejectionsSentExpectedSendDateSendDate------ I have the above columns duplicated for evey section with different names ------- Modeling Section ----TimeToDelivery---- Factory Section ----TimeToCutTimeToSolderTimeToAssembleTimeToWhatever----- Some General ---FinishedDiscardedRemovedI have more columns, but for the purpose of explain my problem, i think this's enough. :-)ThanksThu, 23 Dec 2010 04:43:23 GMTrootfixxxerhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspx[quote][b]rootfixxxer (12/23/2010)[/b][hr]No one... :([/quote]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. ;-)Thu, 23 Dec 2010 03:56:27 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxNo one... :(Thu, 23 Dec 2010 03:20:06 GMTrootfixxxerhttp://www.sqlservercentral.com/Forums/Topic1038342-373-1.aspxHelloI'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? ThanksWed, 22 Dec 2010 09:21:45 GMTrootfixxxer