Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2002
    Posts
    27

    Cool Multiple 1 to 1 "hierarchal" relationships

    I designing a DB and trying to keep it very normalized and using one-to-one relationships and then creating flattened views with insert, update, and delete triggers.

    As an example, both businesses and people have addresses (as well as phone numbers, todo items, notes...). I have created an ENTITY table where the ENTITY has 1 field (EntityID) that has either a 1-to-1 relationship with BUSINESS or a 1-to-1 relationship with PERSON.

    PERSON has fields that are specific to a person (First Name, Last Name, BirthDay...) where BUSINESS has fields that are specific to a business (Corporate Name, Legal Name, FedID#...).

    All the other tables (ADDRESS, PHONE, TODO, NOTES...) have a many to one relationship with ENTITY so I don't have to create multiple tales like PERSONADDRESS, BUSINESSADDRESS.

    The theory extents to creating tables like EMPLOYEE (1-to-1 with PERSON), CONTACT (1-to-1 with PERSON), or CLIENT (1-to-1 with BUSINESS). This would allow the DB to store

    The user interface would look at views that flatten out the tables. Inserts, Updates, and Deletes would be handled by triggers on the base tables and/or views.

    I am new to DB design and this hierarchal structure sounds like a great way to normalize and eliminate duplicate data. I have two questions 1) Is this a good practical structure or is it too academic and convoluted and 2) if this is a good structure, am I correct in setting up the views and triggers?

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

    Re: Multiple 1 to 1 "hierarchal" relationships

    It all sounds good to me. The views and triggers will help applications to work with a simple logical view of a person, etc..

  3. #3
    Join Date
    Nov 2002
    Posts
    11

    Re: Multiple 1 to 1 "hierarchal" relationships

    Originally posted by jackb
    The theory extents to creating tables like EMPLOYEE (1-to-1 with PERSON), CONTACT (1-to-1 with PERSON), or CLIENT (1-to-1 with BUSINESS). This would allow the DB to store
    sorry this answer is rushed and brief but....

    instead of having a seperate table for employee, contact, client you would be better off having a table that models the relationship between entities.

    For example if your Entity's are:
    001 = CompanyA
    002 = CompanyB
    003 = PersonA
    004 = PersonB
    005 = PersonC

    An Entity_Relationship table could be [entity_1, entity_2, relationship_type] then you can show that....
    001 is a client of 002
    003 works for 001
    003 is a client of 001
    004 works for 005
    005 owns 001
    004 is married to 003
    ... the possibilities are endless.

    when you get a new type of relationship you only need to add it to a reference table that is used by the column relationship_type instead of creating a new table. This also means that all relationships are held together rather than in separate tables.

    don't take the above quick answer as gospel... check out "Database Design for Mere Mortals" (Hernandez?) if you can get your hands on it. It explains simply the way relationships work and how you can build models without needing to learn predicate logic and set theory to a PhD level.

    one to one relationships are not that common. don't try to force them upon your database... just make sure you remove many to many's.

    Again... check out the above book.

  4. #4
    Join Date
    Nov 2002
    Posts
    11

    Exclamation Re: Multiple 1 to 1 "hierarchal" relationships

    Originally posted by yalnik
    sorry I also meant to say - views and triggers are certainly important, as are indexes. But first make sure your db model is sound and extensible. if a simple change to your requirements requires you to create a new table, you'll probably find that you could have done things better.

    The relational model is a complex set of methematical principles made pretty simple, but used too complexly (if that's a word) by most people.

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

    Re: Multiple 1 to 1 "hierarchal" relationships

    Originally posted by yalnik
    sorry I also meant to say - views and triggers are certainly important, as are indexes. But first make sure your db model is sound and extensible. if a simple change to your requirements requires you to create a new table, you'll probably find that you could have done things better.

    The relational model is a complex set of methematical principles made pretty simple, but used too complexly (if that's a word) by most people.
    Why do you consider creating a new table to be such a pain? It's very easy: CREATE TABLE t (...);

    Your approach seems to be to take over the functionality of the data dictionary in your own "entity" and "relationship" tables. And what about the attributes - do you suggest a third table for that?

    If that is such a great idea, why don't Oracle et al just provide every database with those 2 or 3 tables instead of letting you create your own?

    Sorry, I think you are WAY OFF with that suggestion!!! You need to read a bit further than "Databases for dummies" or whatever to understand what you would be throwing away with that approach - declarative integrity for example.

  6. #6
    Join Date
    Nov 2002
    Posts
    11
    Tony

    Sorry [head bowed, ashamed!]. i misread your first post and notice that you actually had the entity table in there and are using the person, business, etc as sub-types. When i reread i noticed you were actually on almost the same path as my suggestion (although i had not talked sub-types). the relationship part may or may not be of use.

    your model isn't actually hierarchical (although it appears to be) it's relational with sub-typing (which is better).

    no - it's not too academic... it's logical!

    Stu

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by yalnik
    Tony

    Sorry [head bowed, ashamed!]. i misread your first post and notice that you actually had the entity table in there and are using the person, business, etc as sub-types. When i reread i noticed you were actually on almost the same path as my suggestion (although i had not talked sub-types). the relationship part may or may not be of use.

    your model isn't actually hierarchical (although it appears to be) it's relational with sub-typing (which is better).

    no - it's not too academic... it's logical!

    Stu
    No, my apologies to you! It was late when I wrote my last piece, and I had misinterpreted your meaning. When you refered to tables called "Entity" and "Relationship" I thought you were proposing to use a generic data model for absolutely EVERYTHING in the database!!! I have seen this kind of thing before (there's a company called Lazy Software that flogs a product based on such a two-table design) and I was falsely accusing you of doing that.

    What you were saying makes perfect sense, now I realise that "Entity" is restricted to people and organisations, and "Relationship" is restricted to relationships between people and organisations.

    That will teach me not to post after midnight!

  8. #8
    Join Date
    Nov 2002
    Posts
    11
    I'm in the same boat... quick posts during a break from work lead to misinterpretation and forgetfulness from me.

    "Lazy Software" - what classic name. Half the companies I've dealt with should be called that!

    Yes, the "design" i put forward, that ended up being much the same as the first proposal (when you step back and think about it) is similar to a Contract Management System i worked with here in NZ designed by a company called "Sequel Software". They had an entity called PERORG (person/organisation) which everything was based around. They actually had a denormalised PERORG entity table around which they modelled the rest of their data (the most well normalised commercial DB i've seen). It was a very similar JACKB's design, but without the individual tables for each different entities own attributes (PERSON, ORGANISATION etc). I'm certainly not a fan of denoormalisation at all, but they did do it well (and kept it only to the one table - even if it was about 30 columns)....

    Regards
    Stu

  9. #9
    Join Date
    Nov 2002
    Posts
    27

    Cool Where to place the triggers

    The books tell me to place instead of triggers on the views. Would it make sence to put triggers on the base tables themselves? If that works, I would think that I could avoid having duplicate code on similar views.

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

    Re: Where to place the triggers

    The reason for using "instead of" triggers on the views is to enable DML against the views. For example, if you have a view V_PERSON that combines the ENTITY and PERSON records, then and "instead of" trigger would apply the inserts, updates and deletes to both base tables instead of to the view.

    Example (Oracle syntax):

    CREATE TABLE entity( id pk, name );
    CREATE TABLE person (id pk fk to entity, dob );
    CREATE VIEW v_person AS SELECT e.id, e.name, p.dob FROM entitiy e, person p WHERE e.id = p.id;

    CREATE TRIGGER trig INSTEAD OF insert ON v_person
    BEGIN
    INSERT INTO entity( :NEW.id, :NEW.name );
    INSERT INTO person( :NEW.id, :NEW.dob );
    END;
    /

    INSERT INTO v_person VALUES (1, 'Joe', '23-apr-1981');

    SELECT * FROM entity;

    ID NAME
    1 Joe

    SELECT * FROM person;

    ID DOB
    1 23-APR-1981

    It may be that you would then ALSO have triggers on the base table for other purposes (e.g. auditing).

  11. #11
    Join Date
    Nov 2002
    Posts
    27

    Cool Re: Where to place the triggers

    Thanks...this is a god example of what I woud be using. As far as putting triggers on the base table, I was hoping that I might avoid setting up almost identical triggers multiple times.

    As an example, if the PERSON table had a trigger to create a record in the ENTITY table, that trigger would be fired whether I performed the insert from that view, a similar view (Eg. EMPLOYEE), or directly on the PERSON table (while adminstering the DB). I would probably have to put a small trigger on the view to make sure the triggers fired in the right order.

    What do you think? Any downside?



    Originally posted by andrewst
    The reason for using "instead of" triggers on the views is to enable DML against the views. For example, if you have a view V_PERSON that combines the ENTITY and PERSON records, then and "instead of" trigger would apply the inserts, updates and deletes to both base tables instead of to the view.

    Example (Oracle syntax):

    CREATE TABLE entity( id pk, name );
    CREATE TABLE person (id pk fk to entity, dob );
    CREATE VIEW v_person AS SELECT e.id, e.name, p.dob FROM entitiy e, person p WHERE e.id = p.id;

    CREATE TRIGGER trig INSTEAD OF insert ON v_person
    BEGIN
    INSERT INTO entity( :NEW.id, :NEW.name );
    INSERT INTO person( :NEW.id, :NEW.dob );
    END;
    /

    INSERT INTO v_person VALUES (1, 'Joe', '23-apr-1981');

    SELECT * FROM entity;

    ID NAME
    1 Joe

    SELECT * FROM person;

    ID DOB
    1 23-APR-1981

    It may be that you would then ALSO have triggers on the base table for other purposes (e.g. auditing).

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

    Re: Where to place the triggers

    That sounds right. If there is a rule that must be followed whenever a PERSON is created, then that should be done by a trigger (or constraint preferably, where possible) on the PERSON table. Then if insert is made indirectly, e.g. by insert into a view as I described before, then the rule will still be applied, without any duplication of the rule.

    Not sure what you mean by "I would probably have to put a small trigger on the view to make sure the triggers fired in the right order", though.

  13. #13
    Join Date
    Nov 2002
    Posts
    27

    Re: Where to place the triggers

    As far as the small trigger on the view, I expect that if I run an insert on a view, the view would not necessarily know the order in which to run the insert on the base tables. If it ran an insert on the ENTITY table first and then ran an insert on the PERSON table (which would trigger an insert in the ENTITY table), I would end up with two records in the ENTITY table instead of 1.


    Originally posted by andrewst
    That sounds right. If there is a rule that must be followed whenever a PERSON is created, then that should be done by a trigger (or constraint preferably, where possible) on the PERSON table. Then if insert is made indirectly, e.g. by insert into a view as I described before, then the rule will still be applied, without any duplication of the rule.

    Not sure what you mean by "I would probably have to put a small trigger on the view to make sure the triggers fired in the right order", though.

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

    Re: Where to place the triggers

    Originally posted by jackb
    As far as the small trigger on the view, I expect that if I run an insert on a view, the view would not necessarily know the order in which to run the insert on the base tables. If it ran an insert on the ENTITY table first and then ran an insert on the PERSON table (which would trigger an insert in the ENTITY table), I would end up with two records in the ENTITY table instead of 1.
    OK, that is where the INSTEAD OF trigger comes into play. You would create an INSTEAD OF INSERT trigger on the view, the code of which does an insert just into the PERSON table.

Posting Permissions

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