1 2 Previous Next 19 Replies Latest reply: Apr 22, 2013 1:58 PM by ©J7 Go to original post RSS
      • 15. Re: One Table and A lot of objects which will create dynamically. Solutions?
        Marwim
        If I would be very desperate and needed to implement something that allows the amount of flexibility the OP describes, I would use a mix from Keiths solution to store informations common to each/most advertisements and a XMLTYPE column for the yet undefined attributes. This would allow easy searching for data like "active period" or "net profit". For informations stored in the XML you would still need a customized search.

        Yet I would like to see the front end, that dynamically creates new kind of advertisements and can query the information without the meta data I mentioned.
        • 16. Re: One Table and A lot of objects which will create dynamically. Solutions?
          Billy~Verreynne
          I'm a bit more conservative and will only use the approach that Keith demonstrated well. :-)

          (that said, I have designed and written a PL/SQL app that extensively used XML and dynamic SQL and dynamic web services calls to perform reconciliation between two systems - it worked fine, providing the users had the savvy to ensure that the meta data they provided for recon was valid, and this was not always the case and there's nothing such a system can do about addressing user's lack of understanding and knowledge when the data models to reconcile are dynamically defined)
          • 17. Re: One Table and A lot of objects which will create dynamically. Solutions?
            Peter vd Zwan
            Hi,

            If you realy want to do what you ask, you shout look into the "any types"

            http://docs.oracle.com/cd/E11882_01/server.112/e17118/sql_elements001.htm#i107578

            I would not go there myself but have a look.

            Regards,

            Peter
            • 18. Re: One Table and A lot of objects which will create dynamically. Solutions?
              Keith Jamieson
              I have given this a bit of thought over the last couple of days .

              My initial thought was to have a sub-category table for each category and this would hold all the details required for the search. (

              The advantage is that each search is based on category and changing one category does not affect any other category).

              The disadvantage is you would need a separate screen , query for each category and separate tables populated based on the category

              If you can live with this, it would be a good solution.


              The next thought was to have one table similar as follows:

              advert_id
              category_id
              item_1_name
              item_1_value
              item_2_name
              item_2_value


              However I forsee problems in searching. How do we know what name is in which column.
              I think this will result in performance issues


              So my final thought is to have almost what you suggested in the beginning.

              So we still retain my four tables
              but we have a fifth table

              advert_criteria

              advertiser_id
              advert_id
              category_id
              location
              price
              accomodation_type
              no_of_rooms
              tutor_subject
              tutor_hourly_rate
              etc

              So we add columns when we need to.
              Obviously grouping columns common to all ads together , and then columns specific to each category.

              This will be quite a wide table (eg 50 columns) .

              So now all we have to do is select the appropriate columns based on our category for our searches, and also insert into the appropriate columns for our ad creation.

              The other thing to do is to remove all ads which have expired from this table, probably using a nightly job , as we only want ads to be searched on in here.
              • 19. Re: One Table and A lot of objects which will create dynamically. Solutions?
                ©J7
                Billy Verreynne, it's cool!
                But what about the performance issues? INDEX, PARTITIONS, ...
                I think Object-relational structure is slower than Relative-relational's, isn't it? :(
                1 2 Previous Next