'Is it the best approach? Database modelling, 1-1 Relationship, foreign key as primary key
So I will explain my problem: I have two types of users on my database: Admin and community_user.. Both share several attributes (Name, phone, status...) But admin has an VARCHAR(50) specific field called "Position" as a mandatory field and user has a field called "Vinculation", also a VARCHAR(60) as an optional field. Both are strings typed by the user who have full freedom to type whatever they want to.
Does this modelling make sense? Using primary key as foreign key?
CREATE TABLE User (
PRIMARY KEY (id_user),
id_user INT NOT NULL AUTO_INCREMENT,
phone VARCHAR(50) NOT NULL,
user_status VARCHAR(50)
);
CREATE TABLE Manager (
id_user INT NOT NULL,
PRIMARY KEY (id_user),
FOREIGN KEY (id_user)
REFERENCES User(id_user)
ON DELETE CASCADE,
position VARCHAR(50) NOT NULL
);
/*Testing, you can ignore hehe*/
INSERT INTO User
(phone, user_status)
VALUES
("+5581", "carpe diem" ),
("+5583", "carpe cryy"),
("+5590", "carpe uhuu");
INSERT INTO Manager
(id_user, position)
VALUES
(1,"Sales Manager" ),
(2,"Accounting Manager"),
(3,"Community Manager");
SELECT * FROM User RIGHT JOIN Manager ON User.id_user = Manager.id_user
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

