2

I'm designing a database for a web application project and I came to the conclusion I may have few queries that will require a lot of joined tables to just make one check.

I'm wondering how bad is it to store the foreign key somewhere on the way to decrease number of joins required for these queries?

To give you an example of what I have at this moment: Service => Booking => Transaction => Wallet => BonusOffer

I need to check whether the service has been bought with wallet associated with a bonus. Would it be wise to store BonusOffer id as foreign key of Transaction?

You could ask why Transaction - it's because most of these queries will "go through" Transaction and Transaction will be somewhere in the middle.

philipxy
  • 14,867
  • 6
  • 39
  • 83
pzaj
  • 1,062
  • 1
  • 17
  • 37
  • My heart say you shouldnt, but my mind say you can try if you want. As always check the `EXPLAIN PLAN` to check if you get any improvement. Because sometime you spend a lot of time trying to optimize something the db is already design to optimize, so no need to worry about `too many` joins – Juan Carlos Oropeza Nov 11 '16 at 17:31
  • @JuanCarlosOropeza so does my heart! I thought of using helper tables to link between the `BonusOffer` and `Transaction`. This way I would avoid redundancy and the query would be satisfied. Do you think it's a better practice? In fact I'm pretty sure these queries will be fast anyway, but I'd like to get more information before I actually need to optimize the database for performance rather than do that quickly once I have performance issues. – pzaj Nov 11 '16 at 17:34
  • I dont think is better practice, because is over complicate something and will be outside the normal. That mean will be harder to mantain, and you need to explain to everyone coming behind you about your clever shortcuts. – Juan Carlos Oropeza Nov 11 '16 at 17:45
  • Maybe I am making incorrect assumptions about what a "bonus offer" is, but it seems odd to me that there would not be a link between the transaction and the offer (though perhaps it is the offer that should reference the transaction, maybe a nullable `used_by_transaction_id` field); otherwise how would you know when/where the offer was used (assuming it is not a permanent discount for the "wallet" or something like that). – Uueerdo Nov 11 '16 at 17:50
  • 1
    I think I am having similar thoughts to @Uueerdo. The answer seems to depend on aspects of the data that are not detailed here. If a wallet can have more than one bonus offer associated with it, or the bonus offer impacts the transaction, then the design seems flawed if the column is *not* here, and a combined two-column foreign key (wallet_id, bonus_offer_id) may be appropriate. If bonus offer is not in fact an attribute of the transaction, but only an attribute of the wallet, then including it here is bad design. Avoiding joins is not sufficient motivation for redundant data. – Michael - sqlbot Nov 11 '16 at 23:57
  • 1
    Please give DDL using the minimal columns of your design necessary to illustrate your problem. Please read [ask]. Note that a design question benefits from as many elements of a [mcve] as you can give. PS What @Michael-sqlbot said last comment, except that you should either have the redundant column & FK(s) or no column but appropriate *constraints*. – philipxy Nov 12 '16 at 14:08
  • @Uueerdo A table represents a relationship. The join of the OP's tables gives a table with a relationship on services, bookings, transactions, wallets & offers, with a certain constraint (other than that it's the join of those tables). It doesn't matter what FKs ("links") are in the tables. Whenever a table's subrow values in some column(s) must appear in another table a FK is declared. (Eg an entity id column to an entity table.) The only notion of linking needed is enough entity columns in tables to form a chain from services to their offers in the join. Then add constraints (including FKs). – philipxy Nov 12 '16 at 21:13

1 Answers1

0

Joins are how relational DBMSs work. Learn about and use normalization.

I need to check whether the service has been bought with wallet associated with a bonus.

If this is true for every service then your database is subject to a constraint. It is that (select service from Service_has_transaction join Transaction_has_wallet) is a subset of (select service from Service_has_transaction join Transaction_has_wallet join Wallet_has_bonus).

Most SQL DBMSs don't let you express that constraint declaratively and don't know how to optimize enforcing it. However there is an SQL idiom that we can use to expresss & enforce it declaratively. (Guessing at your table definitions:) First add a bonus column to Transaction_has_wallet and a foreign key from Transaction_has_wallet (wallet, bonus) to Wallet_has_bonus. Then add wallet & bonus columns to Service_has_transaction and a foreign key from Service_has_transaction (transaction, wallet, bonus) to Transaction. This adds redundant columns but nevertheless limits the database to valid states because the foreign key constraints prevent the redundant values from being wrong. (Hopefully this is a motivatig example for learning about expressing arbitrary constraints via triggers.)

philipxy
  • 14,867
  • 6
  • 39
  • 83