Use redundant data in database design when have multiple relation

Question

In my web application users can submit their advertises and buy package for advertise (packages are used for priority of showing advertises)

Database schema is like this:

**Package: id, title, priority(integer)

**PackageDuration: id, package_id(fk), duration, title

**Advertise: id, title, content, ...

**Order: price, user_id, ...

**AdvertisePackage: advertise_id(fk), packageDuration_id(fk), order_id, startDate

to show advertises lists witch is the most used query in application, I should join these tables:

Advertise=>AdvertisePackage=>PackageDuration=>Package

My question is for performance and ease of query I want to add fields In advertise table: (priority-startDate-endDate)

is it Bad design to do so?

Answer 1

It is bad practice to denormalize as you state. Depending on your search criteria, any one of the four tables could be chosen by the query optimization routine. If you are selecting on a package selected from a drop-down list, there should be no reason to add the packages table to the query as you can match the package_id in the package_durations table.

There may be reasons to migrate columns from one table to another. If package duration (start_date, end_date) can be modified after orders have been taken, you may want to copy the duration to the order. If the priority of a package can change, copy it to the orders table, otherwise leave it in the However, migrating columns to simplify the query can lead to issues.

Although many modelling tools will default to id as the surrogate key name, I find it more useful to use a prefix based on the table name. For example use order_id for the orders table (order is a keyword and should be avoided). Having an order table implies you have only one order, while orders implies more than one order. It helps if your table and column names can be used in sentences that clearly state your intent.