This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Apr 22, 2013 11:58 AM by ©J7 RSS

One Table and A lot of objects which will create dynamically. Solutions?

©J7 Explorer
Currently Being Moderated
Hello, friends.
I have a table, let's call "THINGS". I need to contain different objects which will create dynamically like Pencil, Man, Food and etc. Let's think 50 columns will be enough. Which solution would you suggest?
Thanks.
  • 1. Re: One Table and A lot of objects which will create dynamically. Solutions?
    Paul Horth Expert
    Currently Being Moderated
    ©J7 wrote:
    Hello, friends.
    I have a table, let's call "THINGS". I need to contain different objects which will create dynamically like Pencil, Man, Food and etc. Let's think 50 columns will be enough. Which solution would you suggest?
    Thanks.
    I propose you don't do that. What business requirement are you actually trying to achieve?
  • 2. Re: One Table and A lot of objects which will create dynamically. Solutions?
    ©J7 Explorer
    Currently Being Moderated
    I know that it's not good thought. But in my case must do. Objects will create dynamically. Let's think about my example.
    1) Possibility to add new type of objects. (Pencil, Man, Food, and etc. undefined compile-time)
    2) Search any type of objects. (Single interface for search: "Man" or "Pencil" objects)
  • 3. Re: One Table and A lot of objects which will create dynamically. Solutions?
    Paul Horth Expert
    Currently Being Moderated
    ©J7 wrote:
    I know that it's not good thought. But in my case must do. Objects will create dynamically. Let's think about my example.
    1) Possibility to add new type of objects. (Pencil, Man, Food, and etc. undefined compile-time)
    2) Search any type of objects. (Single interface for search: "Man" or "Pencil" objects)
    Remember Oracle is a relational DB. What are the relationships between objects? How do you propose to
    implement these relationships?

    There are many failed projects out there where someone wanted a single table to implement everything in.

    I say again, don't do it.

    You still haven't answered my question: what is the business objective?
  • 4. Re: One Table and A lot of objects which will create dynamically. Solutions?
    Marwim Expert
    Currently Being Moderated
    I don't say, that it cannot be done, but most likely it will become a construct that is hard to implement, slow to query and a nightmare to maintain.

    So you really should tell us more about the business requirements and by all means keep away from EAV (http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model).

    Regards
    Marcus
  • 5. Re: One Table and A lot of objects which will create dynamically. Solutions?
    ©J7 Explorer
    Currently Being Moderated
    This is about Advertisements. There can be different advertisements. A workplace, something for shopping, services, house and etc. I want automate these.
    I don't want to create a table for every Object. It will be hard maintain and it's not dynamic.
  • 6. Re: One Table and A lot of objects which will create dynamically. Solutions?
    KeithJamieson Expert
    Currently Being Moderated
    Why do you only want 1 table.

    I think you need at least 4 tables:
    i) a table to hold the category under which the advert is displayed
    ii) a table to hold contact details for the advertiser
    iii) a table to hold the details of the advert
    iv) a table to hold any images related to the advert.

    eg
    create table Advertiser_Categories
    (
    Category_id   number not null,    
    Category_name varchar2(50 char) not null
    );
    
    category_id is primary key populated with sequence
    
    
    Create Table Advertiser_details
    (advertiser_id   number not null,   -- PK
     first_name       varchar2(50 char) not null,
     last_name        varchar2(50 char) not null,
     email_address varchar2(100 char) not null,
     tel                    varchar2(20 char),
    address             varchar2(20 char) not null
    );
    
    
    create table Advert_details
    (advertiser_id number, 
     advert_id number,
      advert_display_date_from date,
      advert_display_date_to     date,
    advert_text varchar2(32767)
    );
    
    create table Advert_Images
    (advertiser_id number,   -- fk to advertiser_details.advertiser_id
     advert_id number,         -- fk to advert_details.advert_id
     image_id number,
    image blob);
    Based on this, you should be able to insert details of any advert
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 7. Re: One Table and A lot of objects which will create dynamically. Solutions?
    ©J7 Explorer
    Currently Being Moderated
    How do you do SELECT if you want to find houses with 2 rooms?
    How do you do SELECT if you want to find chemistry OR physics tutors?
  • 8. Re: One Table and A lot of objects which will create dynamically. Solutions?
    BluShadow Guru Moderator
    Currently Being Moderated
    ©J7 wrote:
    How do you do SELECT if you want to find houses with 2 rooms?
    How do you do SELECT if you want to find chemistry OR physics tutors?
    If keywords from the adverts are required then you create a relational table to hold keywords and link those words to the adverts, then you can search on them.

    The moment you start thinking you need to do things "dynamically", just think as though you are creating a broken database... it'll be no use to anyone.

    I strongly suggest you go back to the basics of relational database design and learn how to design this properly as others are suggesting.
    Yes, you could use objects within the database to store a variety of information, but querying such data would be difficult compared to a standard relational database design.
  • 9. Re: One Table and A lot of objects which will create dynamically. Solutions?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    ©J7 wrote:

    I have a table, let's call "THINGS". I need to contain different objects which will create dynamically like Pencil, Man, Food and etc.
    Yes, this can be done dynamically use a table as object container for an abstract class and then storing different types of subclass objects into it.

    Simplistic example:
    SQL> create table advertisements of TCustomAd(
      2          ad_identifier   primary key using index
      3  )
      4  /
    
    Table created.
    
    SQL> 
    SQL> create or replace type THouseAd under TCustomAd(
      2          estate_agent    varchar2(100),
      3          house_desc      varchar2(100),
      4          bedrooms        integer,
      5          study           integer,
      6          garage          integer,
      7          pool            integer
      8          -- etc
      9  ) final;
     10  /
    
    Type created.
    
    SQL> 
    SQL> create index housead_bedrooms on advertisements a ( treat( value(a) as THouseAd).bedrooms );
    
    Index created.
    
    SQL> 
    SQL> insert into advertisements values( THouseAd(1,'The House Agency','2 bedroom upmarket apartment',2,0,1,0) );
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> 
    SQL> create or replace type TDeodorantAd under TCustomAd(
      2          brand_name      varchar2(100),
      3          prod_name       varchar2(20),
      4          weight          integer,
      5          picture         blob
      6          -- etc
      7  ) final;
      8  /
    
    Type created.
    
    SQL> 
    SQL> create index deodorantad_weight on advertisements a ( treat( value(a) as TDeodorantAd).weight );
    
    Index created.
    
    SQL> 
    SQL> insert into advertisements values( TDeodorantAd(2,'Smells Nice','Fresh Daisies',250,null) );
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> 
    SQL> select a.* from advertisements a;
    
    AD_IDENTIFIER
    -------------
                1
                2
    
    SQL> 
    SQL> 
    SQL> select
      2          ad_identifier,
      3          treat(value(a) as THouseAd).estate_agent        as ESTATE_AGENT,
      4          treat(value(a) as THouseAd).house_desc          as DESCRIPTION,
      5          treat(value(a) as THouseAd).bedrooms            as BEDROOMS
      6  from       advertisements a
      7  where      value(a) is of (THouseAd) and treat(value(a) as THouseAd).bedrooms >= 2;
    
    AD_IDENTIFIER ESTATE_AGENT         DESCRIPTION                                BEDROOMS
    ------------- -------------------- ---------------------------------------- ----------
                1 The House Agency     2 bedroom upmarket apartment                      2
    
    SQL> 
    SQL> select
      2          ad_identifier,
      3          treat(value(a) as TDeodorantAd).brand_name      as BRAND,
      4          treat(value(a) as TDeodorantAd).prod_name       as PRODUCT
      5  from       advertisements a
      6  where      value(a) is of (TDeodorantAd) and treat(value(a) as TDeodorantAd).weight = 250;
    
    AD_IDENTIFIER BRAND           PRODUCT
    ------------- --------------- --------------------
                2 Smells Nice     Fresh Daisies
    
    SQL> 
    However, one needs to ask why this approach is not used to create a single database table and the simply chucking everything and anything to it as subclasses...

    There are issues such as performance, scalability, and even flexibility.
  • 10. Re: One Table and A lot of objects which will create dynamically. Solutions?
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Billy,

    this is really cool. Something I'm really interested.
    Could you post also the creation of TCustomAd type?

    Is this example working in Oracle 10g?

    <b>Edit:</b>
    Found by myself.
    create or replace type TCustomAd as object(ad_identifier integer ) not final;
    It works in Oracle 10g.

    Regards.
    Al

    Edited by: Alberto Faenza on Nov 12, 2012 5:43 PM
  • 11. Re: One Table and A lot of objects which will create dynamically. Solutions?
    Nicosa Expert
    Currently Being Moderated
    Paul Horth wrote:
    I propose you don't do that.
    I'd propose the same !
    In addition, you might want to take some time and read this story : [url http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/]Bad Carma
    Quite long, but worth it ; Especially the part on +"the devil's triangle of IT"+.
  • 12. Re: One Table and A lot of objects which will create dynamically. Solutions?
    Marwim Expert
    Currently Being Moderated
    This solution must be wrong. When you have a problem like this you have to use regular expressions (a lot of them) and XML ;-)
    There are issues such as performance, scalability, and even flexibility.
    The objects will have little in common, except a very abstract super class. One reason, why I didn't even suggest it. You would need additional meta data to make the handling at least acceptable.

    Regards
    Marcus
  • 13. Re: One Table and A lot of objects which will create dynamically. Solutions?
    6363 Guru
    Currently Being Moderated
    ©J7 wrote:

    I don't want to create a table for every Object. It will be hard maintain and it's not dynamic.
    Unfortunately your proposed solution will be impossible to maintain. If anything happens to the database and it needs to be recreated where will the object creation scripts be?
  • 14. Re: One Table and A lot of objects which will create dynamically. Solutions?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Marwim wrote:
    This solution must be wrong. When you have a problem like this you have to use regular expressions (a lot of them) and XML ;-)
    I'll leave that to those with far superior regex and xml kung fu skillz than me. ;-)
    There are issues such as performance, scalability, and even flexibility.
    The objects will have little in common, except a very abstract super class.
    That is the case in many o-o language implementations and frameworks. Like the TCustomObject in Delphi.
    One reason, why I didn't even suggest it. You would need additional meta data to make the handling at least acceptable.
    Personally, I will not ever use that approach and will make fun on those who do.

    However, it is a kind of interesting thing to mess with and see just what is possible on the edges of the database envelope in Oracle. :-)
1 2 Previous Next

Legend

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