If daughter accounts log in
You can use n:m for a table to relate to itself, I cannot say that this is the best way, however this way you can have "infinite" levels (children, grandchildren, dependents, partner, wife, etc), an example with Mysql and innoDB:
CREATE TABLE IF NOT EXISTS `mydb`.`usuarios` (
`id` INT NOT NULL AUTO_INCREMENT,
`nome` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`usuarios_has_usuarios` (
`usuarios_id` INT NOT NULL,
`usuarios_id1` INT NOT NULL,
PRIMARY KEY (`usuarios_id`, `usuarios_id1`),
INDEX `fk_usuarios_has_usuarios_usuarios1_idx` (`usuarios_id1` ASC),
INDEX `fk_usuarios_has_usuarios_usuarios_idx` (`usuarios_id` ASC),
CONSTRAINT `fk_usuarios_has_usuarios_usuarios`
FOREIGN KEY (`usuarios_id`)
REFERENCES `mydb`.`usuarios` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_usuarios_has_usuarios_usuarios1`
FOREIGN KEY (`usuarios_id1`)
REFERENCES `mydb`.`usuarios` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
The advantage of using foreign key, is that if there are dependent users they will be "tied" to other users. Even if you try to remove the PAI user (assuming it is an accident) the table will not allow this. However you can yes use no foreign key, but if you delete or update something wrong, it will start displaying incoherent data.
An example without a foreign key would be to create an extra column in the users table:
CREATE TABLE IF NOT EXISTS `mydb`.`usuarios` (
`id` INT NOT NULL AUTO_INCREMENT,
`parentId` INT NULL,
`nome` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = myIsam;
Allow NULL in parentId, but you can also use NOT NULL (which probably will stay 0), does not change much.
If daughter accounts do not log in (it is only for saving data)
As I talked to the author, the goal is not to have logins for the children, only saved preference data. In this case I would recommend to create 3 tables, one of "profiles", one for "accounts" and the other for "preferences".
Table accounts and profiles relate 1:n and the preference table relates to the profile table 1:1. It is not necessary, the structure I described works either "with" or "without" the foreign keys, but as I said, the advantage is tying the data.
CREATE TABLE IF NOT EXISTS `mydb`.`contas` (
`id_contas` INT NOT NULL AUTO_INCREMENT,
`login` VARCHAR(45) NULL,
`senha` VARCHAR(45) NULL,
PRIMARY KEY (`id_contas`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`perfis` (
`id_perfis` INT NOT NULL,
`nome` VARCHAR(45) NULL,
`perfiscol` VARCHAR(45) NULL,
`contas_id_contas` INT NOT NULL,
`preferencias_id_preferencias` INT NOT NULL,
PRIMARY KEY (`id_perfis`, `contas_id_contas`),
INDEX `fk_perfis_contas_idx` (`contas_id_contas` ASC),
INDEX `fk_perfis_preferencias1_idx` (`preferencias_id_preferencias` ASC),
CONSTRAINT `fk_perfis_contas`
FOREIGN KEY (`contas_id_contas`)
REFERENCES `mydb`.`contas` (`id_contas`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_perfis_preferencias1`
FOREIGN KEY (`preferencias_id_preferencias`)
REFERENCES `mydb`.`preferencias` (`id_preferencias`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`preferencias` (
`id_preferencias` INT NOT NULL AUTO_INCREMENT,
`preferencia1` VARCHAR(45) NULL,
`preferencia2` VARCHAR(45) NULL,
PRIMARY KEY (`id_preferencias`))
ENGINE = InnoDB;
However if it is a table without a foreign key, it will have to have a columnar to point out the ids of the other tables, for example:
CREATE TABLE IF NOT EXISTS `mydb`.`contas` (
`id_contas` INT NOT NULL AUTO_INCREMENT,
`login` VARCHAR(45) NULL,
`senha` VARCHAR(45) NULL,
PRIMARY KEY (`id_contas`))
ENGINE = myIsam;
CREATE TABLE IF NOT EXISTS `mydb`.`perfis` (
`id_perfis` INT NOT NULL,
`contas_id_contas` INT NULL,
`nome` VARCHAR(45) NULL,
PRIMARY KEY (`id_perfis`))
ENGINE = myIsam;
CREATE TABLE IF NOT EXISTS `mydb`.`preferencias` (
`id_preferencias` INT NOT NULL AUTO_INCREMENT,
`perfis_id_perfis` INT NULL,
`preferencia1` VARCHAR(45) NULL,
`preferencia2` VARCHAR(45) NULL,
PRIMARY KEY (`id_preferencias`))
ENGINE = myIsam;
How to use the darlings
To log in you only need to select contas, and save the id in the session.
SELECT id_contas, login, senha FROM contas WHERE login=? AND senha=?
To display the profiles, you can use something like
SELECT id_perfis, nome, perfiscol FROM perfis WHERE contas_id_contas=? #O ? deve ser o ID que foi salvo na sessão
To view profiles and settings:
SELECT
P1.id_perfis AS ID_PERFIL,
P1.nome AS NOME,
P1.perfiscol AS PERFIL_DATA,
P2.id_preferencias AS ID_PREFERENCIA,
P2.preferencia1 AS PREFERENCIA_1,
P2.preferencia2 AS PREFERENCIA_2
FROM
perfis P1, preferencias P2
where
P1.id_perfis = P2.perfis_id_perfis
What you need is a "one for many" relationship. Create a new "profiles" table and link it to your "users" table".
– Marcelo de Andrade
I think I understand (in theory, the practice is always different), but since the question is open, it is better to wait for someone to answer. Thanks for the tip.
– Florida
I’ll formulate an answer when you’re free. For now, try researching the relationship, I think it will help you take a few more steps.
– Marcelo de Andrade
You can also do it by creating a column
userParentand when a user is created as dependent, just relate the userParent with the userid. Later when determined bond no longer exists between users, just delete the value of userParent.– Papa Charlie