Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2010
    Posts
    1

    Tables with one column and a non-binary relationship

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I won't address all the queries you raised, but a couple:
    The network table only have an id column (i have no other attributes to store), Is it correct to have a table like this?
    It is perfectly acceptable and correct, if unusual, to have a table with only a primary key column and no others.
    In the reservation table the primary kay is the same as a one of the foreign key
    I doubt this is correct, as it means each user can only make one reservation ever, period. You said "Each user can make a reservation (and only once) of a network during a shift", which implies a key of (iduser, idshift) or perhaps (iduser, idshift, idnetwork)?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •