L
L
Lev Bezborodov2014-03-11 02:25:23
SQL
Lev Bezborodov, 2014-03-11 02:25:23

Is architecture denormal? Is it redundant?

Consider the following statement of the problem: there are two classes of subjects, a and b .
Each subject is associated with a single user, and each user is associated with a single subject of one class or the other.
Users can be combined into groups (each user belongs to a single group), groups are respectively of two types, a and b .
Of course, if the user is associated with a subject of class a or b , then the group he belongs to is also of type a or b , respectively.
An example of a database architecture that can implement a solution to such a problem:

CREATE TABLE `group`(`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` TEXT,`type` ENUM('a','b')) ENGINE='InnoDB';
CREATE TABLE `user`(`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` TEXT,`group` INT UNSIGNED NOT NULL,FOREIGN KEY(`group`) REFERENCES `group`(`id`)) ENGINE='InnoDB';
CREATE TABLE `a`(`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` TEXT,`user` INT UNSIGNED NOT NULL,FOREIGN KEY(`user`) REFERENCES `user`(`id`)) ENGINE='InnoDB';
CREATE TABLE `b`(`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` TEXT,`user` INT UNSIGNED NOT NULL,FOREIGN KEY(`user`) REFERENCES `user`(`id`)) ENGINE='InnoDB';

A theoretical question: do I understand correctly that all relations with such a structure belong to the fourth normal form?
A practical question (much more important): does such a structure contain redundancy? If it does, is there any way to fix it? Preferably, humanly, without crutches.
Thanks in advance.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
robofox, 2014-06-30
@robofox

By itself, redundancy does not affect anything (except for the cost of storing data), and sometimes it is useful. It is necessary to evaluate the structure in terms of common sense and efficiency.
For example, why make 2 tables (a and b) if they have the same set of fields? It is possible to make one table with the field ENUM('a','b').
If objects will be added (for example, "c"), then it is more expedient to make a separate table of "user-object" links. If at the same time objects "a", "b" and "c" are in the same table, the number of crutches will be noticeably reduced, performance will increase (if there are indexes).
Also: the `group` field of the user is NOT NULL. What if the user is not a member of any group?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question