This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Apr 22, 2013 11:58 AM by ©J7 Go to original post RSS
  • 15. Re: One Table and A lot of objects which will create dynamically. Solutions?
    Marwim Expert
    Currently Being Moderated
    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?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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?
    KeithJamieson Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points