Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Question My 1st DB app ever, and need a little help with ForeignKeys and Indexes?

    Hi,

    I'm working on my first ever "real" database project.

    It's going to be an email server for my office and I'm keeping user & domain data, and aliases to each of them, in the database.

    I'm going to be using MySQL, and am going through the database design steps.

    I started just copying some of the stuff I found on the net, but I couldn't really get good explanations about WHY they were using the designe they were using, and I got a sense it wouldn't fit anyway for what I wanted.

    So I decided to read up on basics and try to build from scratch, so that at least I'd know what's going on.

    My design's goals are pretty simple:

    (1) I'm going to have multiple email domains.
    e.g.
    domain1.com, domain2.com, ...

    (2) Each domain can have multiple aliases for its domain name.
    e.g.
    domain1.com = domainTHIS1.com, domainTHAT1.com
    domain2.com = domainTHIS2.com, domainTHAT2.com

    (3) Each domain can have multiple user accounts
    e.g.
    @domain.com -> bob(@domain1.com), ted(@domain1.com), alice(@domain1.com)

    And,

    (4) each domain user account can have multiple aliases
    e.g.
    bob(@domain1.com) = sales(@domain1.com), marketing(@domain.com)


    To do this I've set up the following scheme. I *think* I got the 1st three tables ok, although I'm kindof unsure about the INDEX and FOREIGN KEY definitions.

    On the LAST table, `usr_alias`, though, I *think* I need multiple ForeignKeys and multi-column indexes. Honestly, I can't yet get a handle on whether I do, and what it should be.

    If you could take a look at what I got so far, and point out any problems, and especially help me figure out what the last table should be, I'd really appreciate it. I think I've been staring at it too long :-/

    Thanks for helping out!

    Jake

    -- TABLE: `dom`
    CREATE TABLE IF NOT EXISTS `dom` (
    `dom_id` TINYINT NOT NULL AUTO_INCREMENT,
    `dom_name` VARCHAR(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`dom_id`) ,
    INDEX `INDX_dom_name` (`dom_name` ASC)
    ) ENGINE = InnoDB

    -- TABLE: `dom_alias`
    CREATE TABLE IF NOT EXISTS `dom_alias` (
    `dom_alias_id` TINYINT NOT NULL AUTO_INCREMENT,
    `dom_alias_name` VARCHAR(255) NOT NULL DEFAULT '',
    `dom_dom_name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`dom_alias_id`),
    INDEX `INDX_dom_alias` (`dom_dom_name`, `dom_alias_name` ASC),
    CONSTRAINT `FK_domalias_dom_name`
    FOREIGN KEY (`dom_dom_name`)
    REFERENCES `dom` (`dom_name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ) ENGINE = InnoDB,

    -- TABLE: `usr`
    CREATE TABLE IF NOT EXISTS `usr` (
    `usr_id` TINYINT NOT NULL AUTO_INCREMENT,
    `usr_name` VARCHAR(255) NOT NULL,
    `dom_dom_name` VARCHAR(255) NOT NULL,
    `pwd` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`usr_id`) ,
    INDEX `INDX_usr` (`usr_name`, `dom_dom_name` ASC),
    CONSTRAINT `FK_usr_dom_name`
    FOREIGN KEY (`dom_dom_name`)
    REFERENCES `dom` (`dom_name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ) ENGINE = InnoDB;

    -- TABLE: `usr_alias`
    CREATE TABLE `usr_alias` (
    `usr_alias_id` TINYINT NOT NULL AUTO_INCREMENT,
    `usr_alias_name` VARCHAR(255) NOT NULL,
    `usr_usr_name` VARCHAR(255) NOT NULL,
    `dom_dom_name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`usr_alias_id`),
    INDEX ????
    CONSTRAINT ????
    FOREIGN KEY ????
    REFERENCES ????
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ) ENGINE = InnoDB;

  2. #2
    Join Date
    Nov 2011
    Posts
    30
    Quote Originally Posted by JakeT View Post
    Hi,

    I'm working on my first ever "real" database project.

    It's going to be an email server for my office and I'm keeping user & domain data, and aliases to each of them, in the database.

    I'm going to be using MySQL, and am going through the database design steps.

    I started just copying some of the stuff I found on the net, but I couldn't really get good explanations about WHY they were using the designe they were using, and I got a sense it wouldn't fit anyway for what I wanted.

    So I decided to read up on basics and try to build from scratch, so that at least I'd know what's going on.

    My design's goals are pretty simple:

    (1) I'm going to have multiple email domains.
    e.g.
    domain1.com, domain2.com, ...

    (2) Each domain can have multiple aliases for its domain name.
    e.g.
    domain1.com = domainTHIS1.com, domainTHAT1.com
    domain2.com = domainTHIS2.com, domainTHAT2.com

    (3) Each domain can have multiple user accounts
    e.g.
    @domain.com -> bob(@domain1.com), ted(@domain1.com), alice(@domain1.com)

    And,

    (4) each domain user account can have multiple aliases
    e.g.
    bob(@domain1.com) = sales(@domain1.com), marketing(@domain.com)


    To do this I've set up the following scheme. I *think* I got the 1st three tables ok, although I'm kindof unsure about the INDEX and FOREIGN KEY definitions.

    On the LAST table, `usr_alias`, though, I *think* I need multiple ForeignKeys and multi-column indexes. Honestly, I can't yet get a handle on whether I do, and what it should be.

    If you could take a look at what I got so far, and point out any problems, and especially help me figure out what the last table should be, I'd really appreciate it. I think I've been staring at it too long :-/

    Thanks for helping out!

    Jake

    -- TABLE: `dom`
    CREATE TABLE IF NOT EXISTS `dom` (
    `dom_id` TINYINT NOT NULL AUTO_INCREMENT,
    `dom_name` VARCHAR(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`dom_id`) ,
    INDEX `INDX_dom_name` (`dom_name` ASC)
    ) ENGINE = InnoDB

    -- TABLE: `dom_alias`
    CREATE TABLE IF NOT EXISTS `dom_alias` (
    `dom_alias_id` TINYINT NOT NULL AUTO_INCREMENT,
    `dom_alias_name` VARCHAR(255) NOT NULL DEFAULT '',
    `dom_dom_name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`dom_alias_id`),
    INDEX `INDX_dom_alias` (`dom_dom_name`, `dom_alias_name` ASC),
    CONSTRAINT `FK_domalias_dom_name`
    FOREIGN KEY (`dom_dom_name`)
    REFERENCES `dom` (`dom_name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ) ENGINE = InnoDB,

    -- TABLE: `usr`
    CREATE TABLE IF NOT EXISTS `usr` (
    `usr_id` TINYINT NOT NULL AUTO_INCREMENT,
    `usr_name` VARCHAR(255) NOT NULL,
    `dom_dom_name` VARCHAR(255) NOT NULL,
    `pwd` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`usr_id`) ,
    INDEX `INDX_usr` (`usr_name`, `dom_dom_name` ASC),
    CONSTRAINT `FK_usr_dom_name`
    FOREIGN KEY (`dom_dom_name`)
    REFERENCES `dom` (`dom_name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ) ENGINE = InnoDB;

    -- TABLE: `usr_alias`
    CREATE TABLE `usr_alias` (
    `usr_alias_id` TINYINT NOT NULL AUTO_INCREMENT,
    `usr_alias_name` VARCHAR(255) NOT NULL,
    `usr_usr_name` VARCHAR(255) NOT NULL,
    `dom_dom_name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`usr_alias_id`),
    INDEX ????
    CONSTRAINT ????
    FOREIGN KEY ????
    REFERENCES ????
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ) ENGINE = InnoDB;
    Yeah, I can see what you mean about starting at it took long. Take it one step at a time, and just try to define your tables first.

    I was taking a look at your tables and I usually don't read it that way. From how it sounds, it seems like you need a to have a ternary relationship between the Domain, User, and Alias tables.

    I hope this helps.

    Martin

    Martin

Posting Permissions

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