This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Dec 3, 2012 2:53 AM by 936666 Go to original post RSS
  • 15. Re: Dynamic table in Run time
    Purvesh K Guru
    Currently Being Moderated
    933663 wrote:
    Please explain why different users need different tables with different columns. What are they trying to achieve?
    Each user here is each company , each company needs to see only it own data.Data is independent to each company.

    Thanks!
    That does not qualify to create a Separate Table for each company.

    Even heard of Term called "View", that exactly is what you need. You can have all the data for your table, but each customer shall be able to select only a particular set of Data. And this has and should be finalized at the Stage Blu is referting to "Design".

    I think you can mention this to your Design/Architect to consider and that the Architect should put the papers, if not then you can kick him/her, for suggesting such a horrible piece of design.
  • 16. Re: Dynamic table in Run time
    Purvesh K Guru
    Currently Being Moderated
    Exec is an SQLPLUS command and cannot be used in PL/SQL; use this script to call it:
    DECLARE reCode NUMBER;
    errorMsg VARCHAR2(2000);
     begin 
    Create_Table ('mytable','col1name varchar2(10) , col2name varchar2(10)', reCode, errorMsg);          --Provide Width to VARCHAR2 Datatypes.
     end;
  • 17. Re: Dynamic table in Run time
    BluShadow Guru Moderator
    Currently Being Moderated
    933663 wrote:
    Please explain why different users need different tables with different columns. What are they trying to achieve?
    Each user here is each company , each company needs to see only it own data.Data is independent to each company.

    Thanks!
    Still not a valid reason.
    If each company is storing different data then either a) the companies requirements are different so they should have different applications or b) the database design has not been considered sufficiently to provide a fixed design to suit all requirements.

    If you tried to supply us with an application that generated tables at run time and used dynamic code, we'd send it back and ask for our money back as an 'unfit for purpose' application.
    We had a company once who tried to supply us with a Entity-Value-Attribute model database, and that didn't last long before we chucked it.

    Just how is the front end GUI interface for these users going to adapt itself to all the different tables structures?
  • 18. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    so you are suggesting that this can be done in a single table.
    say i can design a table with some 50 columns and i can map them while inserting them based on the company id and for the rest of the columns i can left free with null value.
    Say for my company 1 i need only 1 to 5 column's data type and 25 to 30 column's data type of my main table for rest of the column i can have null values for them, there is no problem if we have more null in the design.


    Thanks!
  • 19. Re: Dynamic table in Run time
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    I want to add to voice to what Blu said. You are lacking a logical and coherent design. There is no data model.

    It all sounds like a hack. And a very bad one at that.

    You are pretty much guaranteed that it will be a mess to maintain. Horrible complex. Totally inflexible. Will not perform at all. And scalability for increasing with increased users and increases in data volumes, will not exist.

    This is NOT how one designs and engineers, software. Or use a RDBMS like Oracle.
  • 20. Re: Dynamic table in Run time
    BluShadow Guru Moderator
    Currently Being Moderated
    933663 wrote:
    so you are suggesting that this can be done in a single table.
    say i can design a table with some 50 columns and i can map them while inserting them based on the company id and for the rest of the columns i can left free with null value.
    Say for my company 1 i need only 1 to 5 column's data type and 25 to 30 column's data type of my main table for rest of the column i can have null values for them, there is no problem if we have more null in the design.


    Thanks!
    That's one way, though you could also design the tables to be relational to deal with different modularisations of the tables.

    e.g. if the information can be broken down into some sort of groups of columns and different companies will use specific groups, then you can create multiple tables (one for each group of columns) and relationally join those together to give the required columns for the specific company. The other tables of groups of columns that they don't use will just remain as empty tables, but then you have the advantage that if a company comes along and decides they now need to store some more data that exists in one of the other groups, you already have the table structures in place and they can be 'switched on' for them to use.
  • 21. Re: Dynamic table in Run time
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    933663 wrote:
    so you are suggesting that this can be done in a single table.
    say i can design a table with some 50 columns and i can map them while inserting them based on the company id and for the rest of the columns i can left free with null value.
    Say for my company 1 i need only 1 to 5 column's data type and 25 to 30 column's data type of my main table for rest of the column i can have null values for them, there is no problem if we have more null in the design.
    You are barking not only at the wrong tree, but are mistaking a telephone pole for a tree and barking at it.

    Forget about this nonsense about creating/designing tables on the fly. IT DOES NOT WORK. EVER. (and yes, I have seem millon US $ projects failing because of such idiocy, and the web is littered with numerous examples of such spectacular failures)

    Show me your data model... oh, but you do not have one!

    And THAT is the problem.
  • 22. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    Then how can i design a table to suit all the conditions any suggestions or ideas how to proceed further
  • 23. Re: Dynamic table in Run time
    Purvesh K Guru
    Currently Being Moderated
    933663 wrote:
    Then how can i design a table to suit all the conditions any suggestions or ideas how to proceed further
    You already suggested one and Blu has another.

    It is not a Job that will take few minutes to complete it. You will have to analyze the data you have and then Map your tables accordingly.

    You can certainly opt for the easier approach of using few columns for a customer and keeping others null; And also to ascertain that a Customer shall have view of only the columns (s)he needs; others will be garbage to them.
  • 24. Re: Dynamic table in Run time
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    933663 wrote:
    Then how can i design a table to suit all the conditions any suggestions or ideas how to proceed further
    You do not start with the design of a table.

    Assuming you have clear and understandable user specifications (the start of the software engineering life cycle), the next step is to design a data model that reflects the business entities and a process model that implements the business flow. Together these two need to meet the goals and requirements stated in the user specification.

    When designing the data model, be very wary of:
    a) the Entity Attribute Value (EAV) design trap
    b) creating opaque entities that will be defined and constructed at run-time

    This forum, and medium, are not suited for running a course on how to design and engineer software solutions. I suggest that you read and research the standard software development life cycle (SDLC), understand why and what every step in the cycle is, what the deliverables are, and how to achieve those. Before moving on to the next step.
  • 25. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    Ok then i will create some 3 or 4 tables with predefined data structure my first step will be over

    View will be used to fetch the data from those tables
    so i need to create a view , creating a view will be unknown until the user chooses his columns , so here i need to create a view which will be dynamic one right ? this is one time job , i can use the view to insert/update/delete/select.

    Thanks!
  • 26. Re: Dynamic table in Run time
    BluShadow Guru Moderator
    Currently Being Moderated
    933663 wrote:
    Ok then i will create some 3 or 4 tables with predefined data structure my first step will be over

    View will be used to fetch the data from those tables
    so i need to create a view , creating a view will be unknown until the user chooses his columns , so here i need to create a view which will be dynamic one right ? this is one time job , i can use the view to insert/update/delete/select.

    Thanks!
    Why is a user choosing his columns, causing you to require dynamic creation of anything?

    The 'requirements' from a user/company should be known before the software is delivered, and the appropriate configuration provided for that user/company when it is delivered.

    So Company A would be provided with a "Company A" set of views/application screens etc. appropriate for themselves.
    and Company B would be provided with a "Company B" set of views/applicaiton screens etc. appropriate for them.
    etc.

    If a user themselves is given the ability to switch on/off parts of the system as they require, then you need to modularise the application and the tables that go with it, so the tables and views will still remain static, and that's where I was talking about having groups of columns on seperate related tables.

    Get away from this idea that things have to be dynamic. They don't have to be, and they most certainly shouldn't need to be.
  • 27. Re: Dynamic table in Run time
    Purvesh K Guru
    Currently Being Moderated
    933663 wrote:
    Ok then i will create some 3 or 4 tables with predefined data structure my first step will be over

    View will be used to fetch the data from those tables
    so i need to create a view , creating a view will be unknown until the user chooses his columns , so here i need to create a view which will be dynamic one right ? this is one time job , i can use the view to insert/update/delete/select.
    Seems like you did not get the point that I, Blu, Billy are trying to Make.
    There is no need of Dynamic Creation of Database Objects.

    Dynamic Creation of Objects, in most cases, imply you do not have a strong data model.

    You can create the View Once+, and that implies it has to be Manual i.e. creation occurs via an Install script which contains explicitly mentioned CREATE VIEW some_name as select column_list from table where some_condition;

    View condition has to be adequate enough to allow Customer to view its data itself and by no means should One Customer peek into other Customers data.
  • 28. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    Blu, Thing for me is that all the things are in online so i may not know how the user will choose his components and so on , so for that purpose i thought of creating a dynamic one at the run time. There are some predefined components are there for the user , users need to pick them up before the start, there will not be any schema change for that user once he pick his schema it is fixed.
    Developer has no idea of what user(company) has chosen all the things are online.

    Thanks!
  • 29. Re: Dynamic table in Run time
    BluShadow Guru Moderator
    Currently Being Moderated
    In that case, it should all be modularised so that the design is fixed per module and the 'user' picks which modules they will be using. The application should be designed to handle the various combinations of modules statically. Without knowing the application, that may present challenges for how you design the front end interface, but that's a seperate issue to the database design, but it would be less of an issue than if you were creating tables and views dynamically.

Legend

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