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