Originally posted by FY Hsieh:
when we talk about table relationship (e.g. one-to-one, one-to-many), does one of them have to hold a foreign key for the other one ? For the following tables
TaxRate(state, city, zip, zip_plus, tax_rate)
Income(state, county, average_income)
the PK for "TaxRate" is a compounding key (state + city + zip + zip_plus)
the PK for "InCome" is also a compunding key (state + county)
the only common field for them is "state". SO neither of the two tables hold any foreign key of the other one.
Since they hold the common field "state", is this a "many-to-many" relation ? i.e. given a row in "Income" table, we have multiple rows in "TaxRate" table, and vice versa. If two tables do not hold any foreign keys for each other, can they still have one-to-one, one-to-many, or many-to-many relationship ?
you would have to aggregate to relate the data in the same way you would a star. in a star you have dimensions/attributes (state, zip, city, etc.) stored in individual dimensions with simple keys. the fact table (income, rate) would have a compounding key containing each of the dimensions.
in your case you would have to aggregate to state to relate your facts.
