First of all i want to say hello (since is my first post here) and say sorry in advance for my english.
I'm having some problems with a database desing, I'll explain you and see if you can help me (thanks in advance).
Let's see: Ill show you my actual code and and the ask y doubts
Code:
CREATE TABLE user
(
iduser tinyint UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
name varchar(10) NOT NULL UNIQUE,
password varchar(10) NOT NULL,
admin boolean NOT NULL DEFAULT 0,
PRIMARY KEY (iduser),
UNIQUE INDEX (user, password)
)ENGINE=InnoDB;
CREATE TABLE shift
(
idshift tinyint UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
day tinyint UNSIGNED NOT NULL,
start time NOT NULL,
end time NOT NULL,
PRIMARY KEY (idshift),
UNIQUE INDEX (day, start)
)ENGINE=InnoDB;
CREATE TABLE network
(
idnetwork tinyint UNSIGNED NOT NULL UNIQUE,
PRIMARY KEY (idnetwork)
)ENGINE=InnoDB;
INSERT INTO netowrk VALUES (1),(2),(3);
CREATE TABLE reservation
(
idreservation tinyint UNSIGNED NOT NULL UNIQUE,
idshift tinyint UNSIGNED NOT NULL,
iduser tinyint UNSIGNED NOT NULL,
PRIMARY KEY (iduser),
FOREIGN KEY (iduser) REFERENCES user (iduser) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (idnetwork) REFERENCES network (idnetwork) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (idtshift) REFERENCES shift (idshift) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;
CREATE TABLE active
(
iduser tinyint UNSIGNED NOT NULL UNIQUE,
idnetwork tinyint UNSIGNED NOT NULL UNIQUE,
started datetime NOT NULL,
PRIMARY KEY (iduser, idnetwork),
FOREIGN KEY (iduser) REFERENCES user (iduser) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (idnetwork) REFERENCES network (idnetowrk) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;
As seen in the code i have three entities: User, Shift, Network.
Each user can make a reservation (and only once) of a network during a shift. THis relationship turns into a the reservation table.
I have to keep an eye also if a network is currently being used. This turns into the active table.
Now i have these doubts:
-The network table only have an id column (i have no other attributes to store), Is it correct to have a table like this? (i thinked of using a DOMAIN instead but im working with mysql and it doesnt support domains)
-I need the database to be in 3NF, Is it?
-In the reservation table the primary kay is the same as a one of the foreign key, if i define the table like this Mysql creates duplicates indexes, Is there a way to avoid that? (don't know maybe change the sintaxys of the difinition)
-Do you think of a better way to do this?
Also id like to ask something about the ER of this database. Im not sure which is the cardinality of each enitity in the non-binary relationship. Now i have it like this:
-User->N
-Shift->1
-Network->1
Because an user only can make a reservation of on shift and network, but the networks and shift can be reserve by many users (although not the same shift-network combination). Is it correct?
Hope you can help with my doubts. Thanks in advance.
dBforums
>
Tables with one column and a non-binary relationship
09-25-10, 11:19
Linear Mode
