2 SQL quesions: address book
-
Hi everyone, this question may seem dumb, so sorry beforehand - I'm just starting to learn SQL :) I want users to be have the address book (set of name/e-mail pairs) in my web application. To do this, I've created a db with 2 tables:
/* User info: */
CREATE TABLE `users`
(
`id` integer (12) NOT NULL AUTO_INCREMENT,
`name` varchar (50) UNIQUE NOT NULL,
`email` varchar (100) NOT NULL,
`password` varchar (50) NOT NULL, /* md5 of password */
PRIMARY KEY (`id`)
)
CHARACTER SET utf8 COLLATE utf8_general_ci;/* Table with address info: */
CREATE TABLE `addresses`
(
`name` varchar (50) NOT NULL,
`email` varchar (100) NOT NULL,
`userid` integer (12) NOT NULL /* id of user (from the 1st table) that stored this record */
)
CHARACTER SET utf8 COLLATE utf8_general_ci;First question: is this a good db structure? Second question: what SQL query (join?) I must perform for user with name "name" to retrieve all his address records? Thank you in advance.
Listen up! "Teamwork" means staying out of my way! (Seifer, Final Fantasy 8).
-
Hi everyone, this question may seem dumb, so sorry beforehand - I'm just starting to learn SQL :) I want users to be have the address book (set of name/e-mail pairs) in my web application. To do this, I've created a db with 2 tables:
/* User info: */
CREATE TABLE `users`
(
`id` integer (12) NOT NULL AUTO_INCREMENT,
`name` varchar (50) UNIQUE NOT NULL,
`email` varchar (100) NOT NULL,
`password` varchar (50) NOT NULL, /* md5 of password */
PRIMARY KEY (`id`)
)
CHARACTER SET utf8 COLLATE utf8_general_ci;/* Table with address info: */
CREATE TABLE `addresses`
(
`name` varchar (50) NOT NULL,
`email` varchar (100) NOT NULL,
`userid` integer (12) NOT NULL /* id of user (from the 1st table) that stored this record */
)
CHARACTER SET utf8 COLLATE utf8_general_ci;First question: is this a good db structure? Second question: what SQL query (join?) I must perform for user with name "name" to retrieve all his address records? Thank you in advance.
Listen up! "Teamwork" means staying out of my way! (Seifer, Final Fantasy 8).
Dmitry Khudorozhkov wrote:
First question: is this a good db structure?
No, you need a primary key on the address table AddressID INT Auto increment. You also need a foreign key between User and address. This means you need to put the userID (currently the ID field on the user table) on the address table. Then to get the address for a user something like
Select *
From Users
inner join Addresses on Addresses.UserID = User.ID
where Users.ID = @IDnoNever underestimate the power of human stupidity RAH
-
Dmitry Khudorozhkov wrote:
First question: is this a good db structure?
No, you need a primary key on the address table AddressID INT Auto increment. You also need a foreign key between User and address. This means you need to put the userID (currently the ID field on the user table) on the address table. Then to get the address for a user something like
Select *
From Users
inner join Addresses on Addresses.UserID = User.ID
where Users.ID = @IDnoNever underestimate the power of human stupidity RAH
Thanks a lot Mycroft.
Listen up! "Teamwork" means staying out of my way! (Seifer, Final Fantasy 8).