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.
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)
If you realy want to do what you ask, you shout look into the "any types"
I would not go there myself but have a look.
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:
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
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.