Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Schema Question 1

Status
Not open for further replies.

d0nny

IS-IT--Management
Dec 18, 2005
278
GB
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?
 
Hi

That is typically solved with a 3[sup]rd[/sup] table :
Code:
[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/][/url]
 
Ah!
I had thought about that but thought it might complicate things slightly for me.

But you are absolutely right. Thanks.
 
change this --
Code:
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 --
Code:
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
 
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/][/url]
 
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
 
Hi

Rudy said:
what about two-letter country codes?
So I have to update all records in the referred tables after a revolution ? :p Ok, this was a joke. But yes, I prefer a numeric id field there too.


Feherke.
[link feherke.github.com/][/url]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top