Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    1

    Multiple entities design doubt

    I have a doubt about the best way to solve this:
    I have a PAYMENTS table with a RECEIVER field, the problem is that the "receiver" may be in two different tables, EMPLOYEES and SUPPLIERS. How can I create a relationship between these tables?
    I thought about an entities table linking them, but then how to query the payments table linking it to the others? I'm using a UNION to add the results for each entity type, but is this the best way to do it?

  2. #2
    Join Date
    May 2002
    Location
    Chicago
    Posts
    22
    It is the best to normalize data - remove the duplication. I don't understand why would you have the same field in both, and if there is any way you can take it out an have the field in only one of the tables, that should work the best.

  3. #3
    Join Date
    Jun 2002
    Location
    Spokane, WA
    Posts
    4

    Re: Multiple entities design doubt

    Originally posted by Yukio
    I have a doubt about the best way to solve this:
    I have a PAYMENTS table with a RECEIVER field, the problem is that the "receiver" may be in two different tables, EMPLOYEES and SUPPLIERS. How can I create a relationship between these tables?
    I thought about an entities table linking them, but then how to query the payments table linking it to the others? I'm using a UNION to add the results for each entity type, but is this the best way to do it?
    I have the same same sort of problem, but on a little larger scale. I'm trying to represent Inventory, but each item needs a very specific description. The items can be broken down into about 5 main categories with lots of stuff in common, but a considerable number of differences as well. Currently, I have an InvItem table and then the 5 sub category tables with a Type field in the InvItem table. This looks like it will work, but the queries will run slower than I would like.

    Has anyone thought of an elegant way to represent what basically amounts to inheritance in a relational database? If you would like to see an ERD of the mess, let me know

  4. #4
    Join Date
    May 2002
    Location
    Chicago
    Posts
    22

    may not be elegant

    Okay, here is what I would do for the problem described:
    Create another table that has a definite number of entries for Receiver - (all possilbe matches) and use it to link the tables that you need. Of course this will only work if receiver has a definite number of possible entries.

  5. #5
    Join Date
    Jun 2002
    Location
    Spokane, WA
    Posts
    4

    Re: may not be elegant

    Originally posted by mariana
    Okay, here is what I would do for the problem described:
    Create another table that has a definite number of entries for Receiver - (all possilbe matches) and use it to link the tables that you need. Of course this will only work if receiver has a definite number of possible entries.
    How does that improve above including 5 IDs in the main table? The worst thing about the query is that I will have to list all attributes of each of the 5 subtables if I run a query on Items rather than a query on a specific item.

    So, imagine that Item included things like cost, price, description, supplier, inv_count, etc. SubTable1 includes color, SubTable2 includes length, width, SubTable3 includes weight, color, packaging, shelf_life...... and so on.

    A query that listed all inventory items would need to include cost, price, etc as well as color, length, width, weight, packaging, and shelf_life even though those fields will not have anything in them most of the time.

    One way to solve this would be to just retrieve data from the main table and then retrieve data from the subtable conditionally, but as I have seen from past experience, this can result in huge amounts of queries being executed.

    Right now, I think I'm resigned to just pull all the data in a single query and then parse through it and create the necessary representations client-side.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yukio:

    > I'm using a UNION to add the results for each entity type,
    > but is this the best way to do it?

    if you want payments from both employees and suppliers, yes

    greg:

    > Currently, I have an InvItem table and then the 5 sub category
    > tables with a Type field in the InvItem table. This looks like it will
    > work, but the queries will run slower than I would like.

    you cannot predict performance; the best you can do is set it up several different ways, load up some meaningful volumes, and test -- but who's got time for that?

    you have a few options

    consider throwing all attributes into one table, and letting them be null where necessary -- that would make a query like the following really efficient

      > A query that listed all inventory items would need to
      > include cost, price, etc as well as color, length, width,
      > weight, packaging, and shelf_life even though those fields
      > will not have anything in them most of the time.

    note that this is the same result you'd get if you did a 6-way left outer join -- nulls in all the places where the subtype didn't match, but the entire result set includes all items

    another option is to put all attributes into a separate table, so that each item will join to the attribute table, and pull out as many rows as needed to describe it -- this means each attribute (color, size, cost, etc.) will need to be coded into an attribute table

    the advantages are that you can pull common attributes (list all items that have a cost, where cost was common to, say, 3 of your 5 subitem types), and furthermore, further enhancements are easy -- imagine what a mess of your code it would be to suddenly be told the week after your app goes live that you have to add a 6th type... all your queries are belong to us!!

    let me know if you need more info, i realize my descriptions here are pretty brief

    rudy
    http://rudy.ca/

Posting Permissions

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