Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2002
    Posts
    2

    Unhappy For the experts Part 1

    Hi
    First, I would like to say I am glad that I have stumbled across this site. I hope that it will clear the headache I have been having for the past two weeks.

    I am currently working as a system designer in a small private college. We provide a range of courses, from English language to university degree.

    The company I work for has established itself as a more of a business than a traditional college. The org structure is one of a business like than an educational like. The courses we offer are in direct link with other institutes and universities. We simply deliver those courses and manage it. In that sense, we are more like a management group managing various courses.

    The company has recently come forth with its obvious destiny. The company sees itself as not a college, rather a management group managing/delivering different products in link with different institutions.

    At current there are 4 products identified.

    1) English language courses
    2) Secondary school courses (year 11/12)
    3) Diploma/Certificate courses
    4) University Degrees

    Now the catch is:

    1) All products have different specifications, not to mention the ever-changing government regulations that model these products. They also have some similarities.

    If I were to have them all under one table called products, it would not work, simply because they have substantial differences in field requirements. However they are all products and they are all courses.

    Is there anyway I can group them all under products and at the same time have them to be different in field structures?

    I have posted my diagram (what I have done so far), which solves the problem with the difference. However, it does not group them into products.

    In addition, could you please advice me of any other problem with my database so far.

    Hope someone can help me!
    Thank you
    Attached Thumbnails Attached Thumbnails test.jpg  
    Last edited by Sann; 09-18-02 at 01:40.

  2. #2
    Join Date
    Jul 2002
    Posts
    55

    Re: For the experts Part 1

    Ack! This is frustrating. I see posts like this almost every couple of weeks on the various forums I troll. This is the first one I've answered and first, my apologies for the commercial-looking post. I'm a pretty laid back geek-coder in reality. Honest!

    Yes it can be done, fairly quickly and with relative ease. You can't do it (quickly and with relative ease) with a conventional relational or even OO database - as far as I know. I've spent the last two years working as a dev for a technology company that has developed (and is continually developing) a web-based application that does exactly this - and more. The technology uses a vanilla RDBMS and some really wacky scripting and has its roots in research that Manchester University undertook into knowledge representation. I can't go into details really - because (a) I'm likely to get fired and (b) it's too complex an application to explain in a simple post. If you want to chat about it further, send me a PM or email.

    Sorry to be so secretive and corporate. Drop me a line if you want to know more.

    Mull.

  3. #3
    Join Date
    Sep 2002
    Location
    Brussel, Belgium
    Posts
    2
    I've been waiting for an answer but i simply can't believe that our current RDMS can't deal with such things.

    I have the same problem. And i've been searching everywhere to find an answer :

    I found theses kind of solutions :

    - Using a general supertype table and linking the subtypes tables with all the primary key / foreign key sharing the primary key of the supertype table (so you'll have only one identifier for your three tables)

    - Using an object oriented DMS ... well i use SQL Server 2000 and thus, i've to deal with that

    - Using one big table that will contains all the attributes (and create views on this table to specialize sections) I'm all but not a big fan of these kind of solutions.

    - SQL 3 Pointers ? A Post on a google's group dealing with the same kind of problem tried to solve this problem with that but it's seems that SQL3 Pointers are evil ... And i didn't find any right example implemanting such things.

    - (My temporary solution) putting x IDs (in your case, like mine it will be 3) in the main table referencing the tables. I only allow one of the field to be filled, others will be null. But you have to do more queries ...

    - When i started (i'm only 21 !) designing web applications i bypass the problem with this (but it's not good programming) i put a field "type" that simply was the table's name that i wanted to reach. And thus the id was the id for that table. First you ask for the table you want to reach, second you query it... Well it worked well but it's 2 queries instead of one (my current temporary solutions is far not better ) but due to the fact that , usually you know what you are asking for when you make a query ... this can be a solution

    - Well i still haven't done an interesting UNION query but it can be a solution. If someone solve this problem with a union query (and explains his implementation) ... please show me

    I've just bought a book descripting how to implement a database from UML diagrams. If i find something interesting i gonna post it here.

    Kindly

    Francois Verbeeck
    Belgium

  4. #4
    Join Date
    Sep 2002
    Posts
    2
    THank you guys for your replay.

    Its a been a great help.

  5. #5
    Join Date
    Oct 2002
    Posts
    7
    sann -

    this is exactly why interfaces and inheritance exist in OO languages . . . you've stumbled upon the dirty secret of relational modeling - you can get from point a to b in a two-tier app, but the journey will be perhaps a bit complicated, and leave you with a solution that feels very contrived in the way it solves a particular business problem.

    in your case, a simple example is that "products" should be a virtual interface to multiple product types (classes). Each specialized product class inherits (or conforms) to a set of base properties and methods for the top class, "product", while providing an extended set of properties and methods that support the specialized type.

    this is somewhat simulated by "typing" rows within a relational db, but you're still a long way off from getting to where you really want to be.

    object/relational tools will help you graft your object model onto an _effecient_ relational table structure . . . one meant to persist your objects, rather than trying to model them.

    a good place to dive headfirst is to pick up a "translated" book concerning Design Patterns in your OO language of choice. after that, you'll discover the real deal w/ the design patterns bible from the 'Gang of Four'.

    the good news for you is two fold:

    - there are plenty of easy to use, well supported OO languages on the market besides C++ . . . visual basic, C#, and Java are the heavy hitters

    - despite the scoffing you may hear, you don't have to eat the whole OO burrito to gain immediate benefit and resolution to some of your design problems here.

    Start out as simple as possible, rip plenty of sample code, use the UML as much as you can stand to, and follow through simple test projects across all 3-tiers to learn how the pieces fit together.


    just my .02 -

    good luck,

    -isaac

  6. #6
    Join Date
    Oct 2002
    Location
    NZ
    Posts
    25

    subtyping

    Sann,

    The relational (and I'd suggest most practical) solution to your problem is to implement subtyping into your model.

    As you say the attribute domains are all 'Products' however they all have different attribute requirements.

    Model a single entity that represents a generalisation of the domain. PRODUCT for example, this table will contain the attributes in common across all the subtype domains - product_id, perhaps a general description of the product, etc.

    The subtype domains will be your highest level general categories, perhaps the ones you have listed, for eg, UNI_DEGREE, DIP_CERT_COURSE etc etc.

    PRODUCT - subtype will be a 1 - M relationship, so the foreign key will be the PRODUCT_ID from the PRODUCT table existing in the subtype table, eg:

    PRODUCT{prod_id, prod_desc... }
    UNI_DEGREE{uni_deg_id, prod_id, uni_deg_desc...}
    DIP_CERT_COURSE{dip_cert_id, prod_id ... }

    do you follow?

    this way you can customise your subtype tables with any required attributes and you still elegantly model the PRODUCT relation.

    Good luck
    Steve

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: subtyping

    Originally posted by steve63
    Sann,

    The relational (and I'd suggest most practical) solution to your problem is to implement subtyping into your model.

    As you say the attribute domains are all 'Products' however they all have different attribute requirements.

    Model a single entity that represents a generalisation of the domain. PRODUCT for example, this table will contain the attributes in common across all the subtype domains - product_id, perhaps a general description of the product, etc.

    The subtype domains will be your highest level general categories, perhaps the ones you have listed, for eg, UNI_DEGREE, DIP_CERT_COURSE etc etc.

    PRODUCT - subtype will be a 1 - M relationship, so the foreign key will be the PRODUCT_ID from the PRODUCT table existing in the subtype table, eg:

    PRODUCT{prod_id, prod_desc... }
    UNI_DEGREE{uni_deg_id, prod_id, uni_deg_desc...}
    DIP_CERT_COURSE{dip_cert_id, prod_id ... }

    do you follow?

    this way you can customise your subtype tables with any required attributes and you still elegantly model the PRODUCT relation.

    Good luck
    Steve
    I absolutely agree with your relational solution; the idea that this pretty standard situation would require (or even justify) an OODBMS is absurd.

    A couple of points I would make:

    1) Surely the PRODUCT - subtype relationship would be 1:1 not 1:M, in which case your tables could be simplified to:
    PRODUCT{prod_id, prod_desc... }
    UNI_DEGREE{prod_id, uni_deg_desc...}
    DIP_CERT_COURSE{prod_id ... }
    i.e. prod_id is both the PK and the FK to PRODUCT in the subtypes.

    2) To facilitate working on subtypes (e.g. to view UNI_DEGREE information), create a view for each subtype that joins the subtype to the supertype, e.g.

    CREATE VIEW uni_degree_view AS
    SELECT p.prod_id, p.prod_desc, ..., u.uni_deg_desc, ...
    FROM product p, uni_degree u
    WHERE u.prod_id = p.prod_id;

    In Oracle, at least, you can also create triggers on the views that enable DML on them for the convenience of applications, e.g. allowing:

    INSERT INTO uni_degree_view (prod_id, prod_desc, ... uni_degree_desc)
    VALUES (...);

  8. #8
    Join Date
    Oct 2002
    Location
    NZ
    Posts
    25

    Re: subtyping

    Originally posted by andrewst
    I absolutely agree with your relational solution; the idea that this pretty standard situation would require (or even justify) an OODBMS is absurd.

    A couple of points I would make:

    1) Surely the PRODUCT - subtype relationship would be 1:1 not 1:M, in which case your tables could be simplified to:
    PRODUCT{prod_id, prod_desc... }
    UNI_DEGREE{prod_id, uni_deg_desc...}
    DIP_CERT_COURSE{prod_id ... }
    i.e. prod_id is both the PK and the FK to PRODUCT in the subtypes.

    2) To facilitate working on subtypes (e.g. to view UNI_DEGREE information), create a view for each subtype that joins the subtype to the supertype, e.g.

    CREATE VIEW uni_degree_view AS
    SELECT p.prod_id, p.prod_desc, ..., u.uni_deg_desc, ...
    FROM product p, uni_degree u
    WHERE u.prod_id = p.prod_id;

    In Oracle, at least, you can also create triggers on the views that enable DML on them for the convenience of applications, e.g. allowing:

    INSERT INTO uni_degree_view (prod_id, prod_desc, ... uni_degree_desc)
    VALUES (...);
    Agree pretty much fully with you Andrew, in particular that attempting to migrate this to an OO model to resolve this particular issue is a complete overkill and highlights the lack of understanding involved with many OO proponents. Anyway I won't start ranting on that (leave it to Fabian :-)

    - I thought the subtype relvar (eg UNI_DEGREE) might be on the many side only because I suspected that the institution might offer multiple degrees, say an IT degree, a Business degree etc. Then the PRODUCT relvar would store the features common to those degrees. Same for Diploma courses etc... Anyway it doesn't matter the PRODUCT_ID will operate as the foreign key in the subtype table whether there are many degree offerings or just 1.

    cheers
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •