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

    Cool Unanswered: Triggers Vs. Stored Procedures

    I'm sure this is an age old argument amount SQL programmers but I'll ask it anyways.

    If I am setting up a "record" from several linked tables (e.g. linking an employee table with address and phone tables into one psudo record)...

    Is it better to set the record up as a view with INSTED OF insert, update and delete triggers OR

    Set up a Select, Insert, Update, and Delete stored procedures.

    As far as I can see it, the View theory allows me to treat this as a self contained object and allows bulk inserts (plus it looks cleaner). The Stored Procedure theory may allow me more programing flexability.

    Besides that, am I missing anything or is there a most accepted method?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should not have your data in separate tables unless you intend to allow an employee to have more than one address, phone number, etc. And if you are allowing one-to-many relationships between the employee table and the secondary tables, your view may or may not be updatable depending on the complexity of the joins.

    Go with stored procedures so that your application can update just the affected elements of the data structure.

    blindman

  3. #3
    Join Date
    Nov 2002
    Posts
    27
    The example I gave is probably not too accurate. I am separating tables to keep common characteristics in the same tables. As an example, both employees and clients have addresses, phones, emails.... To avoid using multiple address & etc tables, I am linknig both employees and clients to a master table called entity. Eveything links off the entity table. (I think it's called sub-typing).

    Originally posted by blindman
    You should not have your data in separate tables unless you intend to allow an employee to have more than one address, phone number, etc. And if you are allowing one-to-many relationships between the employee table and the secondary tables, your view may or may not be updatable depending on the complexity of the joins.

    Go with stored procedures so that your application can update just the affected elements of the data structure.

    blindman

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There are (at least) two other solutions.

    1) Create a single table for employees and clients called "People", or if you want to include businesses in it as well then call it "Entities". The entity record contains information that is common to all entities, such as name and type (I've done this and stored business names in just the LastName field).

    2) Use separate tables for customers, employees, businesses, etc, but use the UniqueIdentifier datatype as the primary key, and populate it using the newid() functionas a default. Now you can have a single phone table where the foreign key is linked to the primary key of all three of the primary tables.

    Option 1 is more functional and versatile, but is also more challenging to code around.

    blindman

  5. #5
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: Triggers Vs. Stored Procedures

    To begin with your initial question: triggers vs. store procs. If you only need to implement the referential integrity and you have SQLServer2k you don't need either trrigers or store procs ecause SQL has a build in referential integrity enforcer that can also handle cascading updates and cascade deletes.

    If you want to do more than that and implement some custom business rule (relaated to your three table version) you should go with triggers because they have some functionality (inserted and deleted tables, INSTEAD OF and so on) that store procs have not and they are called automaticaly by the system when an insert, update and/or delete occurs.

    Now, some theoretical and practical opinions:

    You're right, what you've done it's called sub-typing in the entity model.

    For this sub-typing you have three choices of building the relational model. The first one is your's approach and the other two have been described by blindman. All these methods are acceptable (theoretically speeking) for implemeting sub-typing in relational databases.
    But, there is no rule that tells you what method you should apply. It all depends on real facts, for instance:

    1) If your tables (Employees and Customers) mostly consists of common fields (like FirstName, LastName ...) and have only few fields that are specific only to one entity (eg HireDate for Employees) you should go with one table for both Employes and Customers, and insert a type field that tells you what that record represents (an Employee or a Customer)

    Entity(PK,CommonFld1,...,CommonFldn,EmplFld1,CustF ld1,RecordType), where RecordType for instance can take value 'E' for Employees or 'C' for Customer

    2) If you have many specific fields in your table and very few common fields than you can build two distinct tables one for Employees and one for Customers.

    Customers(PKCust,CommonFld1,CommonFld2,CustFld1,.. .,CustFldn)
    Customers(PKEmpl,CommonFld1,CommonFld2,EmplFld1,.. .,EmplFldn)


    3) last, but not the least, if you have many common fields and, also, many specific fields you can choose your's approch in which you build three tables.

    In their first table you put all the common fields and the primary key, let's call it Entity:

    Entity(PK, CommonFld1,...,CommonFldn)
    Employees(PKEmpl,EmplFld1,...,EmplFldn)
    Customers(PKCust,CustFld1,...,CustFldn)

    In this last approach fields PKEmpl and PKCust both have two roles: first they are primary keys in their tables and also they are foreign keys for PK in Entity table.


    All above was the theoretical part, now the practical:

    All methods described have their adv and disadv.

    <B>1st method (one table approach)</B>

    Adv:
    You don't need to replicate you common fields accross many tables
    You can easily filter only the employees or customers as you wish
    When you add/change record(s) for an employee and/or a customer this is a one table operation

    , BUT
    Disadv:
    First, if there are many specific fields in you table than some of them will alawys get null value depending on the type of record. For a record indicating an employee (RecordType='E'), all the speciffic fields for customers shall remain NULL. You also have to create rules for that behaviour, if you want consistency in you database.
    Second, and more important. What if, one of your employee is also a customer of your company.

    Now you have several choices:
    - insert two records for the same person one with RecordType='E' and the other with the RecordType='C'
    -give another value for RecordType in order to support both subtypes, eg: RecordType='B' means that distinctive record represents both an employee and a customer. Now you should change all your existing queries to support this new value of RecordType
    - delete the RecordType field from your Entity table and chek if a record is a customer or an employee by testing against null the value of some specific fields. This means that you also have two change your forms that are adding/changing records for employees and customers, because if you are "adding" a new customer, you n=must first check if an employee with the same common fields isn't already exists in your database and the other way around.


    <B>2nd method (two tables approach)</B>
    Adv
    You have employees and customers separated in their own tables, so there is no confusion.
    When you add/change record(s) for an employee and/or a customer this is a one table operation
    You don't have to have null values for some of yours field because they are now grouped in tables acording they specific needs.

    Disadv.
    If there are many common fields than you replicated them accross many tables.
    If one customer can be also an employee now you have no choice than to insert two distinctive records one in the Customer table and the other in the Employees table. You have to do that because of your's relations with, I dont' know, the invoices table for instance that is related with Customers table, and if you have to insert your employee in the customer table also, in order to have it "related" to an invoice.
    If you want to track Employees that are also Customers you should add another table in your database EmplCust(PKEmpl,PKCust), and invent some sort of algoritm to populate that table.


    <B>3rd method (three tables approach)</B>
    Adv
    You don't have nulls in your fields because the common fields are in the Entity table, and the specific fields are in their coresponding tables.
    You can have an employee that is also a customer, you simply add just one record in the entity table, one record in the employee table and one record in the customer table.
    You can easily sort and/or filter all the entities, just the customers, just the employyes or just the employees that are also customers.

    Disadv
    When you add/change/delete record(s) for an employee and/or a customer this can be a two table operation:
    for inserting new records is always a two tables operation and this can be quite difficult to implement.
    for changing records it can be a one table opeartion (if all the modified fields are either common or specific fields) or a two table operation (if some of the modified fields are common fields and some are specific)
    for deleting a record you have another one or two tables operation, because if you want to delete an employee, which is also a customer than you should delete only the coresponding record in the Employees and not the one in the Entity table because than all the common information for the customer will be lost too.


    That's all I can think now, about this.

    Any way the decision is yours. You should choose the method that best meet your needs.


    Good luck,

    ionut

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    there is disadvantage using ENTITIES containing both common and specific fields. actually there 2 disadvantages. first, the table may become very wide. second, it will be a challenge to properly index it. I always put only common fields into one table that describes different entities, e.g.:

    addresses (addressid, entitytypeid, addr1, addr2, city, etc.)

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Agreed in principle. I wouldn't add additional tables just to save one or two null columns.

    blindman

  8. #8
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    Blindman the main problem is not to save some space. The problem appers when (in our specific case) an employee is also a customer. What about then? You add him twice in the table, once as a customer and once as a employee?????


    Originally posted by blindman
    Agreed in principle. I wouldn't add additional tables just to save one or two null columns.

    blindman

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Absolutely not. You either determine logically his status from relationships to other records, or you have a field in the entity record to determine type. You could have multiple boolean fields for different types, or single integer field using a binary numbering system to store multiple values.

    blindman

  10. #10
    Join Date
    Nov 2002
    Posts
    27
    I definitely am going with option #3. I am dealing with situations where an employee of a client may be a contact for an unrelated business and a client may be a client of an employee. In fact, I am probably going to create a many-to-many table called relationships just to handle all the permutations.

    My real concern was whether to use triggers or stored procedures. Sounds like triggers are the way to go as long as I straighten out the cascading order logistics.

Posting Permissions

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