AXON SG wrote: hi i currently have 3 tables which are joined through a syn key... assuming the tables are table1, table2 and table3 and the syn key is fieldA and fieldB.... if i remove fieldA from the key, does it mean that if i select the fieldA from table1.fieldA using a list box, i will not be able to get data from table2? will there be any difference if i select use table2.fieldA or table3.fieldA? |
So you have this?
table1: fieldA, fieldB, other fields
table2: fieldA, fieldB, other fields
table3: fieldA, fieldB, other fields
Now, when you say "if I remove fieldA from the key", what do you mean? Removing it from one or more of the tables? Assuming the tables should be keyed by both fieldA and fieldB as shown, then removing fieldA from one or more of the tables will break the data model.
For the above, the right way to "fix" it (if you consider it broken) is to combine all three tables into one. They all have the same key, so in a sense, they're all the same table.
Perhaps all this will make more sense if we replace these abstract table and field names with actual table and field names and data.
Units Sold:
Customer, Product, Units Sold
Andy, Pencils, 400
Bob, Pencils, 250
Carla, Pens, 200
-----------------------------
Revenue:
Customer, Product, Revenue
Andy, Pencils, 600
Bob, Pencils, 500
Carla, Pens, 200
-----------------------------
Cost:
Customer, Product, Cost
Andy, Pencils, 500
Bob, Pencils, 300
Carla, Pens, 100
These tables are easily joined, and almost certainly should be:
Sales:
Customer, Product, Units Sold, Revenue, Cost
Andy, Pencils, 400, 600, 500
Bob, Pencils, 250, 500, 300
Carla, Pens, 200, 200, 100
However, even if they aren't joined, and you allow the synthetic key to form, if you build a chart with customer and product as dimensions, and have expressions summing the other fields, you'll see exactly what is shown in the Sales table, even though the sales table doesn't exist. The data model stores equivalent information.
Now, you suggested removing fieldA from the key, but you still mention referring to table1.fieldA. So let's assume you mean removing it from tables 2 and 3. That's equivalent to removing Customer from the Revenue and Cost tables. Now what do we have?
Units Sold:
Customer, Product, Units Sold
Andy, Pencils, 400
Bob, Pencils, 250
Carla, Pens, 200
-----------------------------
Revenue:
Product, Revenue
Pencils, 600
Pencils, 500
Pens, 200
-----------------------------
Cost:
Product, Cost
Pencils, 500
Pencils, 300
Pens, 100
And what happens if we build a chart? Well, we no longer associate customer with revenue or cost, so the total revenue and total cost will be associated with only the product. Anyone buying that product will get the full revenue and cost from everyone buying the product. In other words:
Customer, Product, sum("Units Sold"), sum(Revenue), sum(Cost)
Andy, Pencils, 400, 600+500=1100, 500+300=800
Bob, Pencils, 250, 600+500=1100, 500+300=800
Carla, Pens, 200, 200, 100
And that is, quite simply, wrong. Removing Customer from any of the tables breaks the data model.
Answering one of your questions directly, "does it mean that if i select the fieldA from table1.fieldA using a list box, i will not be able to get data from table2?" No, it means that you'll get TOO MUCH data from table2. But I'm still not sure I've understood what you mean by removing fieldA from the key, so perhaps I've gone off on a lecture on the wrong subject.
