13 Replies Latest reply on Apr 15, 2012 6:20 PM by Christy H.

    Geralization of DB

    Christy H.
      Hello,

      I want to discuss here about my design of the web based application.

      There are nine modules of the website and each module has:

      1) Location i.e. Region, Country and City
      2) Category
      3) Status
      4) User Preferences
      5) User Favorites

      I generalized these modules (from point 1-5) and added a table WebsiteSection and then created sub tables to save point 1-5 listed above. The design of my DB became neat and clean but now I am thinking to repeat point 2 to 5 for each module of the website to reduce the work load and raise the efficiency of data retrieval.

      I am confused and stuck between these two separate opionions. Please advise what should I do?

      Thanks & best regards
        • 1. Re: Geralization of DB
          If you repeat 2) to 5) for eacj module you will have a denormalized design with redudant tables.
          As a properly build application is scalable the reason you specify to do this must be qualified as nonsense.

          ---------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Geralization of DB
            Christy H.
            Thanks sir for your favorable reply

            What I conceive from your message is that DE-normalized design with redundant tables are better. Is that right?

            Thanks & best regards
            • 3. Re: Geralization of DB
              No, that is NOT right.
              I was indicating you should NOT do this.
              If you need attributes by module, just add a module id as extra column and include it in the primary key.
              -----------
              Sybrand Bakker
              Senior Oracle DBA
              • 4. Re: Geralization of DB
                Christy H.
                Thanks again for favorable reply

                I did like this e.g.

                I created webModules table and then sub table say UserPreferences. I deemed as webModule as master table and inserted its reference to UserPreference table as foreign key and tried to save UserPreferences for all modules of the website but worry about:

                1) data efficiency
                2) The most important that if something become wrong with these two tables then all modules of website related to UserPreferences section will suffer and may cause user annoyance. On the other hand if problem occurs in one module UserPreferences table then other modules will work properly and finding the problem will be easy

                This is my point of view but for sure I am not expert but this is my general view that I want to discuss here.

                Please advise
                • 5. Re: Geralization of DB
                  Christy H.
                  Am I right?
                  • 6. Re: Geralization of DB
                    sb92075
                    the data in the tables should be Normalized

                    http://en.wikipedia.org/wiki/Data_normalization
                    • 7. Re: Geralization of DB
                      No, but as you ignore my explanation and apparently insist on implementing redundant, denormalized tables, resulting in a disastrous design, I won't pursue this further and stop responding in this thread.

                      -----------
                      Sybrand Bakker
                      Senior Oracle DBA
                      • 8. Re: Geralization of DB
                        sb92075
                        Christy H. wrote:
                        Am I right?
                        If I agreed with you, then we would both be WRONG!
                        • 9. Re: Geralization of DB
                          rp0428
                          >
                          2) The most important that if something become wrong with these two tables then all modules of website related to UserPreferences section will suffer and may cause user annoyance. On the other hand if problem occurs in one module UserPreferences table then other modules will work properly and finding the problem will be easy
                          >
                          And what is something becomes wrong with the 'webModules' table? That will cause problems in all modules.

                          You can keep the data for modules logically separate by using partitioned tables; one partition for each module. That way the tables are normalized but the data can be accessed and managed separately.
                          • 10. Re: Geralization of DB
                            Christy H.
                            Thanks all for helping me

                            Sorry Sir, I said earlier that I am not an expert and your opinion is valuable for me. I was just trying to clear my silly ideas. Please check http://www.onlinenics.com/db.jpg
                            I design this by following the directives provided me in this post. Please point out if still it need modifications

                            Best regards
                            • 11. Re: Geralization of DB
                              Christy H.
                              It means still something wrong in my designed that nobody replied. Have I not followed your directives accurately?
                              • 12. Re: Geralization of DB
                                sb92075
                                Christy H. wrote:
                                It means still something wrong in my designed that nobody replied. Have I not followed your directives accurately?
                                1) Do you realize & understand that all responses here are from unpaid volunteers?
                                2) Do you realize & understand that for most folks around the world this is a weekend day?

                                If you are ever dissatisfied with the tone, tenor, completeness, correctness, or timeliness of any response,
                                please submit your request for a prompt & full refund to /dev/null


                                By the way, NOBODY here owes you any answer at all.
                                • 13. Re: Geralization of DB
                                  Christy H.
                                  I am sorry for any inconvenience and I appreciate as always OTN community helped me and I had opportunity to prove my knowledge

                                  I again apologize and best regards