[SOLVED] SQL tables help

[ShaolinF]

Hey guys,

 

I am working on my table structure and am alittle stuck on what to do. The user has the ability to reg himself, and if he doesnt then his details wont be saved but his transaction will. Now, when I store the transactions for regged and unregged users should I store them in different tables ? See the tables below:

 

User Table

UserID

Name

Contact

Email

Gender

Registered

username

password

moderator

 

Event Table

EventID

EventName

MaxNoTickets

SingleTicketPrice

 

Purchase Table

PurchaseID

UserID

EventID

NoOfTicketsPurchased

TotalPrice

 

As you can see, the following table would only work if the user was registered. But now the question is how I should store events for unregistered users. So far it looks like I will need to create a seperate table.

[PC Nerd]

not necicarily.  if those are all the details... then simplay make the reigstered field say "False" probably a Boolean value would be best.  Then you simpyl check to see if they are registed befroe usign the other data.

 

gdlk

[awpti]

As PC Nerd said,

 

Here's my table layout for users:

 

CREATE TABLE `otn_users` (
  `id` int(11) NOT NULL auto_increment,
  `role_id` int(11) NOT NULL,
  `username` varchar(32) default NULL,
  `password` varchar(40) default NULL,
  `visible_name` varchar(32) NOT NULL,
  `date_created` datetime default NULL,
  `email` varchar(128) default NULL,
  `verify_key` varchar(40) default NULL,
  `validated` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

 

The 'validated' field is an INT value of 0 (not registered) or 1 (registered).

 

No need to store unregistered users in another table.

[ShaolinF]

Excellent. I wanted to know, can you have more than 1 foreign key in a table ?

[ShaolinF]

As PC Nerd said,

 

Here's my table layout for users:

 

CREATE TABLE `otn_users` (
  `id` int(11) NOT NULL auto_increment,
  `role_id` int(11) NOT NULL,
  `username` varchar(32) default NULL,
  `password` varchar(40) default NULL,
  `visible_name` varchar(32) NOT NULL,
  `date_created` datetime default NULL,
  `email` varchar(128) default NULL,
  `verify_key` varchar(40) default NULL,
  `validated` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

 

The 'validated' field is an INT value of 0 (not registered) or 1 (registered).

 

No need to store unregistered users in another table.

Thanks. How comes you used single quotes around the column names ?

 

 

[cooldude832]

The whole point of primary keys is so you can cross reference tables. 

Your events table should include the primary key of the creator so a query for event data might look like (this is my standard pagination search query)

<?php
		$fields = array(
			POSTINGS_TABLE.".PostingID as Post_ID",
			POSTINGS_TABLE.".Title as Post_title",
			POSTINGS_TABLE.".Address as Post_addr",
			POSTINGS_TABLE.".State as Post_state",
			POSTINGS_TABLE.".Zip as Post_zip",
			POSTINGS_TABLE.".Price as Post_price",
			POSTINGS_TABLE.".Year_Built as Post_yr",
			POSTINGS_TABLE.".Employee as Post_employ",
			POSTINGS_TABLE.".Seating as Post_seat",
			POSTINGS_TABLE.".Parking as Post_park",
			POSTINGS_TABLE.".Description as Post_descrip",
			POSTINGS_TABLE.".Date_posted as Post_date",
			POSTINGS_TABLE.".Date_updated as Post_update",
			POSTINGS_TABLE.".Sq_ft as Post_size",
			POSTINGS_TABLE.".Image_type_0 as Post_img_0",
			POSTINGS_TABLE.".Image_type_1 as Post_img_1",
			POSTINGS_TABLE.".Image_type_2 as Post_img_2",
			POSTINGS_TABLE.".Image_type_3 as Post_img_3",
			USERS_TABLE.".Username as User_username",
			USERS_TABLE.".Email as User_email",
			USERS_TABLE.".Suspend as User_suspend",
			USERS_TABLE.".State as User_state",
		);
		$fields = implode(" , ",$fields);
		$q = "Select ".$fields." from `".USERS_TABLE."`, `".POSTINGS_TABLE."` 
		Where ".USERS_TABLE.".Suspend != '1'  and ".POSTINGS_TABLE.".PosterID=".USERS_TABLE.".UserID 
		and ".POSTINGS_TABLE.".Date_updated >= '".date("Y-m-d G:i:s", strtotime("-60 Days"))."' 
		".$where." 
		Group by ".POSTINGS_TABLE.".PostingID
		Order by ".$orderby." 
		LIMIT ".$lower_limit.", ".$deals_per_page;
		$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
?>

This returns all the data retaining to a given posting (I used constants to define my events table (POSTINGS_TABLE) and users table (USERS_TABLE)

It returns results were the user isn't suspended (You would do registered/not registered) and it returns results were the date it was updated last is less than 60 days ago.  The only trick to doing the two table part is the group by the PostingID and the POSTINGS_TABLE.".PosterID=".USERS_TABLE.".UserID  this aligns all the user data to a given post were that user id was found, and the whole point of cross referencing my UserIDs into my events table by this field called PosterID.

 

 

Does this clear things up?

[ShaolinF]

Thanks cooldude. I think whats kind of confusing is if they dont reg then i will have most of their details anyway. and if they purchase tickets again then how will I allow them if they for example use the same email add (email being unique key) ? Below is what I have done for the user table, suggestions:

 

CREATE TABLE user (

userId integer auto_increment NOT NULL,

name varchar(100) NOT NULL,

contactNo varchar(12) NOT NULL,

email varchar(80) NOT NULL,

gender varchar(1) NOT NULL,

Registered tinyint(0) NOT NULL,

username varchar(40) default NULL,

password varchar(40) default NULL,

moderator tinyint(0) NOT NULL,

administrator tinyint(0) NOT NULL,

disabled tinyint(0) NOT NULL

PRIMARY KEY  (userId),

UNIQUE KEY username (username),

UNIQUE KEY email (email)

)

 

Im not sure if I should keep the admin/mod in the same field on in a different table. Suggestions ?

[cooldude832]

primary keys for any table and there is only a 0.000001% chance it will not be this is a bigint auto incremented.  Period it needs to be a unique value that is made on your server that is simple to reference.

 

If you want unique email addresses check for it before being entered.

[ShaolinF]

Ok, but if user A registers with jack@hotmail.com - Then another user decides to reg a second account under jack@hotmail.com (you know some people and fake emails  :-\). Now if User A requests his password, who's details will he recieve ? His or the other dude's ? You have 2 different users with the same email.

[cooldude832]

that is why you make people verify their email address before approving account.

 

When this happens you either say

a) allow this new registration to replace the old if its hasnt' been activated for X days

b) tell them to email admin support from said account to get the email addresses

 

 

There is no perfect, but its way better than your idea

 

[teng84]

primary keys for any table and there is only a 0.000001% chance it will not be this is a bigint auto incremented.  Period it needs to be a unique value that is made on your server that is simple to reference.

 

If you want unique email addresses check for it before being entered.

 

" unique email address " thats it

 

and in your question it depends on how you query but the default will be the first entry.. it always order by id in default..

 

[ShaolinF]

I have decided not to allow registrations. The process is short and simple and I'll just keep it that way. Thanks for the help though guys.