DBA Question: Best way to use FK's

What and when is the best way to use FK's without geting FK "redundancy".

Let's say that i have three tables Account, Category and Product.

1: Table Account

Definition:

• Id, Primary BigInt
• Name, Varchar

2: Table Category

Definition:

• Id, Primary BigInt
• AccountId, BigInt ForeignKey
• Name, Varchar

3: Table Product

Definition:

• Id, Primary BigInt
• AccountId, BigInt ForeignKey - "the black sheep"
• CategoryId, BigInt ForeignKey
• N开发者_StackOverflow中文版ame, Varchar... and so on

My question(since im no DBA): Is it bad design/plain stupid to use FK AccountId on table Product, when I know that my FK CategoryId points to a table which holds the same FK?

I'm just thinking in terms of the DRY-principle.

Why im wondering is, let's say that I want to get all of the products for Account x. If I don't have the FK AccountId on Product, I'll always have to join the Category table in order to fetch all products for Account x. Which can be quite a performance hit, I'm guessing.

If you have a opinion, and I mean ANY opinion on this subject. Please literatly KILL this thread by writing your point of view on how you think FK's should be used.

Thanks!

Robin

As almost everything in programming, it's "depends".

If your it's a natural relationship from your business model, so yes, you should model it that way. If you're just trying to speed up a search, you can deal with that by creating indexes, for instance.

IMO, you should never start modeling your database looking for performance. First thing is to create a model which represents correctly your business domain and to reduce duplicate data. You can do that by normalizing your database.

After that, if you start code your application and that data extracting starts to be a problem, you should go with indexes creation. If that doesn't works well, so them you should go to de-normalization way.

Modeling your database in your proposed way can lead to some problems. Consider Account A with Category C:

• What should happen if you create a Product P with that FKs in place?
• What happens if you needs to change a product category?
• Do you need to keep track for category history, like "what was a product category two months ago"?
• A product really "belongs" to an Account? So two accounts with same product needs that product to be added twice?

It's ok the way you do it (model based development frameworks will use the FK's to build entry forms automagically and let you associate a product with an account, e.g), but it's not flexible in some aspects.

Categories are associated with accounts which is not that good as you can only use the category table for that association and not in a generic way (you could use categories for all kind of relations).

There is no Many-to-Many-Relationship between products and accounts which you could create by a separate table. At the moment every product has a specific category and account.

You can't create a category without an account first and so on.

Your table structures look a bit odd. Are you trying to store a list of products sold for a specific account and categorise those products? If so I'd do it this way:

Account
    ID
    Name

Category
    ID
    Name

Product
    ID
    Name
    CategoryID

AccountProduct
    ID
    AccountID
    ProductID

The last table holds product sales for an account.

Is it bad design/plain stupid to use FK AccountId on table Product, when I know that my FK CategoryId points to a table which holds the same FK?

IMO yes. This design isn't in third normal form because of that column, and there's no real need to bring in a denormalisation at this point.

If I don't have the FK AccountId on Product, I'll always have to join the Category table in order to fetch all products for Account x.

Yes.

Which can be quite a performance hit, I'm guessing.

No, not really. Databases are designed for exactly this kind of operation; you should be perfectly comfortable using simple inner JOINs like these in your queries.

You do sometimes need denormalisations for performance reasons. But not here.