1 3 4 5 6 7 8 Previous Next 116 Replies Latest reply: Mar 3, 2006 5:46 AM by cd_2 Go to original post RSS
      • 60. Re: SIMPLE Database Design Problem !
        Tony Andrews
        I work on a system that has over 1000, and it really isn't a problem.
        How many people did write the system and how many are there to maintain the requirement changes ?
        About 30 and 20. But the number of tables doesn't govern this - it is the complexity of the requirements and the sheer amount of ongoing changes (driven by customers and legislation). Creating and modifying tables is probably 1% of the work. Of course, the data requirements must be analysed first, but that would be equally true if we used your One True Concrete Elephant approach - unless you intend that the customers should configure their own additional entity types, attributes, relationships?
        • 61. Re: SIMPLE Database Design Problem !
          488423
          About 30
          hmmmmm we do not plan on investing that kind of money.
          • 62. Re: SIMPLE Database Design Problem !
            cd_2
            About 30
            hmmmmm we do not plan on investing that kind of money.
            If you plan to use equal amount of "entity types", who do you think is going to maintain them? The customer?

            C.
            • 63. Re: SIMPLE Database Design Problem !
              488423
              If you plan to use equal amount of "entity types".
              We do have "many many" entityTypes,and also the customer can add any thing entityType or RelationType on the fly.

              We are maintaining the system with 3 developers.

              I guess the worst thing we do is we consume the disk badly but we have 5 prices that we must be include.

              1.Harddisk................fast enough but we use the space badly
              2.CPU......................no CPU load at all.
              3.RAM......................no RAM load at all.
              4.Development...........building the "meta-object designer" costs
              5.Maintainance..........The number of lines in the project is is one to ten..less code to maintain so less people.

              PS:We also had 4 versions of the project on 4 different database designs.

              Ver 1.0>>>>>>>>>seperated tables....................costs too much maintain with people
              Ver 2.0>>>>>>>>>8 tables but generic................high RAM cost
              Ver 3.0>>>>>>>>>object database holding data....high CPU cost
              Ver 4.0>>>>>>>>>object db holds the logic ,RDBMS data.....high cost at disk low cost,average speed,low CPU,low RAM , low maintainance
              • 64. Re: SIMPLE Database Design Problem !
                cd_2
                If you plan to use equal amount of "entity
                types".

                We do have "many many" entityTypes,and also
                the customer can add any thing entityType or
                RelationType on the fly.
                And has to maintain the object db as well if he/she wants to access the data - or how should that work?
                We are maintaining the system with 3 developers.

                I guess the worst thing we do is we consume the disk
                badly but we have 5 prices that we must be include.

                1.Harddisk................fast enough but we use the
                space badly
                2.CPU......................no CPU load at all.
                3.RAM......................no RAM load at all.
                4.Development...........building the "meta-object
                designer" costs
                5.Maintainance..........The number of lines in the
                project is is one to ten..less code to maintain so
                less people.
                I still doubt that you're refering to lots of different entities here ... otherwise how could you save on business rules, unless the entities share the same rules?
                PS:We also had 4 versions of the project on 4
                different database designs.

                Ver 1.0>>>>>>>>>seperated
                tables....................costs too much maintain
                with people
                Ver 2.0>>>>>>>>>8 tables but
                generic................high RAM cost
                Ver 3.0>>>>>>>>>object database holding data....high
                CPU cost
                Ver 4.0>>>>>>>>>object db holds the logic ,RDBMS
                data.....high cost at disk low cost,average speed,low
                CPU,low RAM , low maintainance
                Ver 4.0: And will explode, if you really try to use 10 mio. records ...

                C.
                • 65. Re: SIMPLE Database Design Problem !
                  cd_2
                  Deconstructing the Elephant

                  Since the originator of this thread offered some details, I'm going to analyze his approach a bit more into depth - after all it could be the next best thing after sliced bread.

                  "We do have "many many" entityTypes,and also the customer can add any thing entityType or RelationType on the fly."

                  What's a typical many to many relationship?

                  First one, that comes to my mind, could be customers vs. suppliers,

                  1 Costumer can have one or more suppliers = 1:m
                  1 Supplier can have one or more customers = 1:n

                  therefore: customer:supplier = m:n

                  And if I don't dig too deep into details, customers and suppliers could look alike, both could have a name, an addresse, etc. So why not move them into one entity and distinguish between them through an entity type?

                  One problem could be, if you want to make sure, that there are only customer:supplier relationships, no customer:customer and no supplier:supplier. With a entity relationship modell with customer and supplier entity plus a customer_supplier relationship, all I would have to do is add some foreign key constraints. In the "elephant" design, I need to code, since for now, foreign keys are column, not column value based.

                  What if I want to add another entity to the customer:supplier relationship, for example rebates?

                  In the entity relationship modell I would have to add (maybe) some fields to the customer_supplier relationship or I just add a unique id to it, add a new entity called rebates and a customer_supplier_rebate relationship - depending on what I want to achive.

                  In the "elephant" modell I would have to extend the relationship table to connect to other relationships/entities, and again there would be code to make sure that there are no other relationships.

                  So far, the attempted simplicity by stuffing everything into one entity table slowly dissolves.

                  C.
                  • 66. Re: SIMPLE Database Design Problem !
                    Niall Litchfield
                    Performance will suffer significantly.
                    as there is is clustered index on
                    entityType...there is NO performance decrease.

                    Cool trick, unfortunately Oracle does not have
                    clustered indexes.
                    Errrm it does. They are called Index Organized Tables. Doesn't mean no performance decrease or that the design is good - it isn't.
                    • 67. Re: SIMPLE Database Design Problem !
                      488423
                      And has to maintain the object db as well if he/she wants to access the data - or how should that work?
                      Type information knowledge level is in the object database.Where we define commposition and inheritance.(Domain Model) POEAA
                      Knowledge level does not contain operatinal data.Operational data is viewed using Views in RDBMS.
                      I still doubt that you're refering to lots of different entities here ... otherwise how could you save on business rules, unless the entities share the same rules?
                      That's where we like the system as there is a generic object model in the object database the bussiness rules are defined using an interpreter pattern.(GOOD grammar oriented object Design from Arsanjani)
                      Ver 4.0: And will explode, if you really try to use 10 mio. records ...
                      it is true that this schema gets really slower without clustered index after 10 million records.The clustered index stores the same entityType records all together...
                      With a entity relationship modell with customer and supplier entity plus a customer_supplier relationship, all I would have to do is add some foreign key constraints.
                      There is a "mismatch" when it comes to complex object graphs.Such as inheritance composition or complex structures.We did not invented this "mismatch".
                      Errrm it does. They are called Index Organized Tables.Doesn't mean no performance decrease or that the design is good - it isn't.
                      Thanks I will try that. :=)
                      • 68. Re: SIMPLE Database Design Problem !
                        cd_2
                        And has to maintain the object db as well if he/she
                        wants to access the data - or how should that work?

                        Type information knowledge level is in the object
                        database.Where we define commposition and
                        inheritance.(Domain Model) POEAA
                        Knowledge level does not contain operatinal
                        data.Operational data is viewed using Views in
                        RDBMS.
                        I ask again: if the customer adds a new entity type, does he/she have to maintain the object database, or how do the business rules to this new type get into the system?
                        I still doubt that you're refering to lots of
                        different entities here ... otherwise how could you
                        save on business rules, unless the entities share the
                        same rules?

                        That's where we like the system as there is a generic
                        object model in the object database the bussiness
                        rules are defined using an interpreter pattern.(GOOD
                        grammar oriented object Design from Arsanjani)
                        Generic modells work only for generic rules - what about individual rules per entityType?
                        Ver 4.0: And will explode, if you really try to use
                        10 mio. records ...

                        it is true that this schema gets really slower
                        without clustered index after 10 million records.The
                        clustered index stores the same entityType records
                        all together...
                        So would partitions. Again: have you tested your complex views with this mass of data?
                        With a entity relationship modell with customer and
                        supplier entity plus a customer_supplier
                        relationship, all I would have to do is add some
                        foreign key constraints.

                        There is a "mismatch" when it comes to complex object
                        graphs.Such as inheritance composition or complex
                        structures.We did not invented this "mismatch".
                        Is this supposed to be an answer to my question? Could you please translate that? Maybe with an example of how you would solve the given problem I described?

                        C.
                        • 69. Re: SIMPLE Database Design Problem !
                          488423
                          I ask again: if the customer adds a new entity type, does he/she have to maintain the object database, or how do the business rules to this new type get into the system?Generic modells work only for generic rules - what about individual rules per entityType?
                          There are 6 basic classes.
                          Entity,EntityType,Relation,RelationType,Attribute,AttributeType

                          For Bussiness Rules (5 main)
                          Rule Class....composite to entityType
                          Composite Rule Class....inherits Rule Class
                          Constant Class..........composite to AttributeType and inherits Rule class.
                          Binary Operation.......inherits composite Rule Class
                          TableLookup Class.......inherits composite Rule Class and composite to attributeType Class

                          all bussiness rules are in object database as they are a part of knowledge level.
                          More information about GOOD can be found at. www.arsanjani.org/GOOD/

                          So would partitions. Again: have you tested your complex views with this mass of data?
                          Yes I am, but the speed difference gets noticed after millions of rows.(trade off)
                          Maybe with an example of how you would solve the given problem I described?
                          in the domain model in object database it consists of 2 Groups(context Maps)

                          A.SuperEntity Level EntityTypes

                          SuperEntity

                          Classification > to avoid unneccessary inheritance
                          Category

                          Description > Value structures that are composite to SuperEntity
                          UnitOfMeasure
                          Attachment
                          Identification
                          Date

                          Structure > To define the relations as distinc objects when needed.

                          B. Main EntityTypes..........inherit SuperEntity

                          Artifact >abstarct entityTypes that customers can use.
                          Campaign
                          Facility
                          Contact_Mechanism
                          Document
                          Lead
                          Line_Item
                          Objective
                          Order
                          Party
                          Price_List
                          Quote
                          Requirement
                          Situation
                          Transaction
                          • 70. Re: SIMPLE Database Design Problem !
                            cd_2
                            I ask again: if the customer adds a new entity
                            type, does he/she have to maintain the object
                            database, or how do the business rules to this new
                            type get into the system?Generic modells work only
                            for generic rules - what about individual rules per
                            entityType?


                            There are 6 basic classes.
                            Entity,EntityType,Relation,RelationType,Attribute,Attr
                            ibuteType

                            For Bussiness Rules (5 main)
                            Rule Class....composite to entityType
                            Composite Rule Class....inherits Rule Class
                            Constant Class..........composite to AttributeType
                            and inherits Rule class.
                            Binary Operation.......inherits composite Rule Class
                            TableLookup Class.......inherits composite Rule Class
                            and composite to attributeType Class
                            I'm getting the impression that we're talking about yet another meta-language.
                            all bussiness rules are in object database as they
                            are a part of knowledge level.
                            More information about GOOD can be found at.
                            www.arsanjani.org/GOOD/
                            So the customer can add some rules in the context of the provided object database.
                            So would partitions. Again: have you tested your
                            complex views with this mass of data?

                            Yes I am, but the speed difference gets noticed after
                            millions of rows.(trade off)
                            As long as your customers can buy bigger hardware ...
                            Maybe with an example of how you would solve the
                            given problem I described?


                            in the domain model in object database it consists of
                            2 Groups(context Maps)

                            A.SuperEntity Level EntityTypes

                            SuperEntity

                            Classification > to avoid unneccessary
                            ry inheritance
                            Category

                            Description > Value structures that are
                            re composite to SuperEntity
                            UnitOfMeasure
                            Attachment
                            Identification
                            Date

                            Structure > To define the relations as
                            as distinc objects when needed.

                            B. Main EntityTypes..........inherit SuperEntity

                            Artifact >abstarct entityTypes that
                            at customers can use.
                            Campaign
                            Facility
                            Contact_Mechanism
                            Document
                            Lead
                            Line_Item
                            Objective
                            Order
                            Party
                            Price_List
                            Quote
                            Requirement
                            Situation
                            Transaction
                            And that does answer my question for the given problem how? Which one of those given EntityTypes relates to another EntityType which again relates to another EntityType as shown in my example? That's all I wanted to know, not a meaningless list ...

                            Somehow we're getting more and more into Vodoo-IT [1] ...

                            C.
                            [1] A term I coined (or maybe read somewhere) for concepts that don't answer questions but automagically do everything as intended - at least by the definition of their inventors.
                            • 71. Re: SIMPLE Database Design Problem !
                              488423
                              >>I'm getting the impression that we're talking about yet another meta-language.
                              So the customer can add some rules in the context of the provided object database.


                              Yeap,DSL....domain specific languages.

                              >>As long as your customers can buy bigger hardware ...

                              that' true.Bigger means disk wise,it is a tradeoff(cpu,RAM versus disk)
                              By the way disk is the cheapest element right now.Also the views which does not contain relationships are really close to other designs.


                              >>And that does answer my question for the given problem how? Which one of those given EntityTypes relates to another EntityType which again relates to another EntityType as shown in my example? That's all I wanted to know, not a meaningless list ...


                              Sorry my bad.I tried to give u examples of the entityTypes that are represented in the object database which are constructed at run-time.These entityTypes do not exist in design time,only in runtime.

                              The Relations are designed by the entityType "Structure".It has two relations(commisionner,responsible) to superEntityType which is the abstract entityType for the main entityTypes.it is the "Accountability Pattern"s from Martin Fowler.(Analysis Patterns)
                              • 72. Re: SIMPLE Database Design Problem !
                                6363
                                Cool trick, unfortunately Oracle does not have
                                clustered indexes.
                                Errrm it does. They are called Index Organized Tables. Doesn't mean no performance
                                decrease or that the design is good - it isn't.
                                I don't believe they are directly equivalent, since they can have different requirements, are stored differently, and may be used for different reasons.

                                http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1308402202736#53716900931676

                                I don't deny that there are also many similarities between an IOT in Microsoft SQL Server's clustered indexes, but it would appear clustered indexes are used more frequently in SQL Server than IOTs are in Oracle.

                                Of course most of what I know of SQL Server is "book learning" so I could be wrong.
                                • 73. Re: SIMPLE Database Design Problem !
                                  cd_2
                                  The Relations are designed by the entityType
                                  "Structure".It has two
                                  relations(commisionner,responsible) to
                                  superEntityType which is the abstract entityType for
                                  the main entityTypes.it is the "Accountability
                                  Pattern"s from Martin Fowler.(Analysis Patterns)
                                  And that does reflect my question how? Would you please just read my example again and show me how that would work in your denormalized modell?

                                  Since you like this kind of style, I'm referring to so called "weak entities".

                                  C.
                                  • 74. Re: SIMPLE Database Design Problem !
                                    488423
                                    I don't believe they are directly equivalent, since they can have different requirements, are stored differently, and may be used for different reasons.
                                    Hmmm,I have tried it and thanks, the performance increases significantly.
                                    Deconstructing the Elephant.What's a typical many to many relationship?First one, that comes to my mind, could be customers vs. suppliers,
                                    First of all the form looking in from our object model in object database the supplier and Customer are PARTY entityType.They are the classifications for the PARTY entityType.To define a many to many relationships we use an entityType "STRUCTURE" which is composite to "SUPER_ENTITY" and related to twice to "SUPER_ENTITY"(commimissioner,Responsible).

                                    As SUPER_ENTITY is the base entityType for PARTY(Customer,Supplier).The many to many relations are modelled through the "STRUCTURE" entityType.if the relationShip has attributeTypes,u can easliy add them "STRUCTURE" entityType.As it defines the structures that entityTypes have with other entityTypes.

                                    PS1:One thing about the many to many relations that we also use a "LINE_ITEM",but it is all a different story,where u can find from "Data Model Patterns" David Hay.

                                    PS2:in order to have a visualisation u can look at the pictures section for UML description of the object model that is constructed at runtime.

                                    http://groups.yahoo.com/group/EnterPriseModelling/......it might not be the latest version but it still will give u the idea.
                                    1 3 4 5 6 7 8 Previous Next