Is using redundant relationships bad?

Question

Suppose my database has the following tables:

tables http://www.freeimagehosting.net/uploads/a5ef036857.png

Now all my queries depend on the company table. Is it bad practice to provide each table with a table (redundant) relationship to a company table in order to simplify my sql queries?

Edit 1: Background - usage issue with the framework. See Django: Constraining Model Data .

Edit 2: No tuple will change its company.

Edit 3: I am not writing mysql queries. I am using an abstraction layer (django).

Answer 1

This is bad practice because your redundant data needs to be updated independently and therefore redundantly. A process fraught with the possibility of error. (Even automatic cascading must be assigned and maintained separately)

By entering this relationship, you are actually denormalizing your database. Denormalization is sometimes necessary for performance, but from your question, it sounds like you are simply simplifying your SQL.

Use other mechanisms for the abstract complexity of your database: Views, Stored Procs, UDFs

Answer 2

What you are asking is whether to break the third normal form in your design. This is not something that needs to be done without a good reason, because by creating redundancy, you are creating the opportunity for errors and inconsistencies in your data. In addition, “simplifying” the redundant data model to support some operations can complicate other operations. In addition, restrictions and other data access logic may need to be duplicated unnecessarily.

Answer 3

Is it wrong to use every table (redundant) relation to the company table to simplify my SQL queries?

Yes, absolutely, because that would mean updating every redundant relationship when updating a customer relationship with a company or company division - and if you miss such an update, you will now have a database full of redundant data. This is bad denormalization.

---

If you just want to simplify your SQL, consider using views to “wrap” parent data. Here is the view that pulls company_id into the contract, by attaching to the client:

create view contract_customer as select a.*, b.contract_id, b.company_id from contract a join customer b on (a.customer_id = b.customer_id); 

This connection is simple, but why repeat it again and again? Write it once, and then use the view in other queries.

Many (but not all) RDBMSs can even optimize the connection if you do not put any columns from the client in the selection list or where a view-based query suggestion is made if you do contract.customer_id restriction of the referential integrity of the foreign key on client.customer_id. (In the absence of such a restriction, the union cannot be omitted, since then there will be a .customer_id contract that does not exist in the client. Since you will never want this, you will add a foreign key constraint.)

Using a view provides what you want, without the overhead of time to update the child tables, without the additional cost of creating more detailed rows, adding a redundant column (and it really starts to matter when you have many rows, because the wider the row, the fewer lines can immediately fit into the memory), and most importantly, without the possibility of inconsistent data when the parent is updated, and the children are not.

Answer 4

If you really need to simplify the situation, View (or several views) will be useful here.

The presence of a column for the company in the view of your employee will not be poorly normalized if it is obtained from the connection section.

Answer 5

If you mean adding a company column to each table, this is a bad idea. This will increase the potential for data integrity problems (i.e. will be changed in one table, but not on the other 6, where it should be).

Answer 6

I would say that this is not the case with OP, but sometimes it is useful (like goto;).

Joke:

I work with a database where most tables have a foreign key pointing to the root table for the accounts. Account numbers are external to the database and cannot be changed after they are issued. Thus, there is no danger of changing account numbers and not updating all links in the database. I also found that it is also much easier to grab data from tables with a key by account number, rather than perform complex and costly hierarchy joins to get into the root account table. But in my case, we do not have as many foreign keys as the external (that is, the real world) identifier, so it is not quite the same as the situation with the OP and seems suitable for exclusion.

Answer 7

It depends on your functional requirements for Performance. Will your application be in high demand? Simplification JOINS boasts performance. In addition, equipment is cheap and turnaround time is important.

The deeper you go into the normal forms of the database - you save space, but hard to calculate