Schema Question 1

[d0nny]

I'm putting together my own mailing list manager (built in PHP and mySQL) and I have a question on managing users.

My thoughts were to have two DB tables: 1. emails table and 2. Lists table.
The link would be that each user in the 'emails' table would have a key to the 'Lists' table. That work fine.

The main downfall of this simple system is that there is a direct 1 to 1 relationship between a user and a list.
What I am now thinking is how do I get a user to be on more than one list?

Is there a better design?

 

[feherke]

Hi

That is typically solved with a 3[sup]rd[/sup] table :

[b]create[/b] [b]table[/b] user [teal]([/teal]
  id integer [b]primary[/b] [b]key[/b] [b]auto_increment[/b][teal],[/teal]
  name [maroon]varchar[/maroon][teal]([/teal][purple]100[/purple][teal]),[/teal]
  email [maroon]varchar[/maroon][teal]([/teal][purple]50[/purple][teal])[/teal] [b]not[/b] [b]null[/b]
[teal]);[/teal]

[b]create[/b] [b]table[/b] list [teal]([/teal]
  id integer [b]primary[/b] [b]key[/b] [b]auto_increment[/b][teal],[/teal]
  name [maroon]varchar[/maroon][teal]([/teal][purple]100[/purple][teal])[/teal] [b]not[/b] [b]null[/b]
[teal]);[/teal]

[b]create[/b] [b]table[/b] userlistlink [teal]([/teal]
  id integer [b]primary[/b] [b]key[/b] [b]auto_increment[/b][teal],[/teal]
  userid integer [b]not[/b] [b]null[/b] [b]references[/b] user [teal]([/teal]id[teal]),[/teal]
  listid integer [b]not[/b] [b]null[/b] [b]references[/b] list [teal]([/teal]id[teal]),[/teal]
  [b]unique[/b] [teal]([/teal]userid[teal],[/teal] listid[teal])[/teal]
[teal]);[/teal]

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[d0nny]

Ah!
I had thought about that but thought it might complicate things slightly for me.

But you are absolutely right. Thanks.

 

[r937]

change this --

create table userlistlink (
  id integer primary key auto_increment,
  userid integer not null references user (id),
  listid integer not null references list (id),
  unique (userid, listid)
);

to this --

create table userlistlink (
  userid integer not null references user (id),
  listid integer not null references list (id),
  primary key (userid, listid)
);

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[feherke]

Hi

Yepp, that is the logical choice and I used to write it like that.

But there were cases which made me take a decision : no table without id field. ( Do not ask the reason. I happily forgot it. That means, I never regretted the decision. )

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[r937]

no table without id field"

[sigh]

what about two-letter country codes?

there are dozens of counter-examples like that

please, keep an open mind

think of the children...



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[feherke]

Hi

what about two-letter country codes?

So I have to update all records in the referred tables after a revolution ? Ok, this was a joke. But yes, I prefer a numeric id field there too.


Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]