Simple table... or two

trefrog · #1 (**permalink**) 01-23-06, 15:11

I'm making a website with PHP and MySQL that has features available only to subscribed members. This is my first time doing this kind of website, and I'm new to PHP and MySQL. I think I have all the necessary fields worked out.

Code:

Table 'users'
 'ID' unsigned mediumint(8) primary auto_increment
 'username' varvhar(20) unique
 'password' varchar(32)
 'email' varchar
 'SubType' ENUM('Single','Family','Institutional','Corporate','Sustaining')
 'ContactName' varchar
 'CorpName' varchar
 'Address' varchar
 'Address2' varchar
 'CityTown' varchar
 'ProvState' varchar
 'Country' varchar(44)
 'PostalZIP' varchar
 'Phone' varchar
 'PayMethod' ENUM('PayPal','Cheque_MO','Cash')
 'Newsletter' ENUM('FALSE','TRUE') NOT NULL
 'Recur' ENUM('FALSE','TRUE') NOT NULL
 'SubStart' date
 'SubEnd' date
 'SubStatus' ENUM('Active','Expired','Pending','Terminated')
 'LastVisit' date
 'visits' unsigned mediumint(8)

After making this, I thought it looked kinda big.
I have 2 questions:
1) Would I benefit from splitting this into two or more tables?
2) Are there any issues that I should consider before setting this 'in stone', so to speak? Does anything look funny, or like it could be done in a better way? (I know that's 3 questions, but it's basically the same question..)

Teddy · #2 (**permalink**) 01-23-06, 15:51

I would store subscription information in a seperate set of tables. The demographic information alone is fairly valuable, for example, how would you track recurring subscriptions, or perhaps users who cancel and then re-sign?

trefrog · #3 (**permalink**) 01-23-06, 17:19

I'm sorry, maybe I don't quite get what you're saying.. the 'recur' field is TRUE, if the user checks the 'automatically renew my subscription' box. If a subscription expires (or is canceled.. I don't know why they would cancel before their time is up), and they wanted to re-activate their account, then I could just reset the 'substart' date, clear the 'subend' date, and set their status back to 'active'... unless they could just go thru the initial subscribe process again and create a whole new record, but then they wouldn't be able to use the same username... unless I removed the unique attribute and did a check with php for uniqueness before they sign up, but that sounds costly, not to mention poor practice (is it?).

Wow, this is getting complicated.

Ok-

If I did separate the SubType, PayMethod, Recur, SubStart, SubEnd, SubStatus, LastVisit and visits fields into another table, then I could somehow link each user in the 'users' table to multiple records in the 'other' table for when there's a break in the continuity of a subscription. Then the history of that user would not be destroyed. That's more advanced than I know yet tho. Is that possible? How would I do that?

EDIT: nvm, that's kindof a stupid question... got it. one-to-many relationships, reading a great tutorial now: http://www.pizzabytheslice.com/SQLClass/RDBMS_part2.PDF

trefrog · #4 (**permalink**) 01-23-06, 17:54

I think I'm going to split them up. I can't see any reason not to. Any other suggestions?

Internet Services

Web Development

Digital Marketing

Consumer Tech