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.

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.

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.

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/