Database design

Databases
1

I’m designing a dating-style site and am trying to figure the best way to set up my tables. Right now I have a ‘users’ table including id, username, email, and password and a ‘user_profile’ table including about twenty columns for every question under the sun like: eye color, DOB, age, and etc.

Should I create a userid in my ‘user_profile’ table and attempt to attach a unique id for every visitor that fills out the form to correspond with a column? This sounds kind of unreasonable. Rather, id like to store the answers gather in another table under a column called ‘user_answers’ have the userid column there but the logistics of transferring the data sounds tricky for a newbie. To add another layer of complexity, these fields have to be updatable.

Because there’s a many-to-one relationship between users and profile attributes, viz., selecting blue eyes should return many profile matches, I would like to keep them separate.

2

I would try it like this.


create table users
( id int not null auto_increment primary key
, first_name varchar(99)
, last_name varchar(99)
, user_name varchar(99)
, DOB DATE
, password varchar(99)
) engine=innodb

create table user_attributes
( id int not null auto_increment primary key
, user_id int not null
, attribute_type varchar(99)
, attribute varchar(99)
) engine=innodb

the user_attributes table would contain data like this


| id | user_id | attribute_type | attribute | 
| 1  |   1     |  eye colour    |   blue    |
| 2  |   1     |  hair colour   |   brown   |
| 3  |   1     |  beard colour  |   NULL    |

you can either show a beard colour to be null, if there is no beard or just not have that row.

That’s my first thought. Is it close to you were looking for?

bazz

3

what bazz said…

… but without the auto_increment in the user_attributes table

:slight_smile:

4

Thanks for the advice. I’m doing pretty much what you suggested.

I have two more questions, first: what is engine+innodb mean? I’ve been using utf8 character set.

And second, right now I have three levels of service for the site – basic, standard, and premium. Basic is free while standard and premium will be paid portions of the site. Right now I have it set up with three directories named the same. I had an idea of attaching an id to the users table where it could be inner joined to the members_level table which is then joined to member_desc table.

I realized fast that I can’t use user.id to join them because of the auto_increment feature I need to keep track of individual users as they accumulate. So I thought to create another key that gives users a static id of 1, 2, or 3. If they pay they get either a 2 or a 3 depending on the level of service they request at the time. Also, those keys need to be updatable if the user decides later to upgrade their service.

My problem: right now I have three directories that I want the appropriate level of user to automatically be taken based on their level of service (1,2,or 3). It would be ridiculous for them to have to choose every time they logged on. Also, the way the site is set up now, they couldn’t even log into the basic part of the site if they had a standard or premium membership which is absurd.(I don’t need them to go to any lower level–unpaid level–since all upper directories will feature what the lower levels have +). However, I don’t want my users to become confused they make a mistake and click basic as a standard or premium member.

How can I make the site whisk them to the correct level based on their userid of 1, 2, or 3? Is this possible the way I have site currently structured? Thanks a bunch.

5

this isn’t really a database question, is it

:slight_smile:

6

Are you mixing up the concepts of user_id and privilege_id?

If I understand what you are trying to do; I would have a user_privileges table like this


| user_id | privilege |

user_id FK’s back to the users table and privilege FK’s to a new table called privileges


| privileges |

so when they log in, your query can associate the user with their privileges.

you could even store the directory name for each privilege in the db so your website knows which directory to use. However, I don’t understand why you have different sites based on privilege. seems like a design flaw and, as rudy says, that isn’t a database issue.

7

[QUOTE=IBazz;4774727]I would try it like this.


create table users
( id int not null auto_increment primary key
, first_name varchar(99)
, last_name varchar(99)
, user_name varchar(99)
, DOB DATE
, password varchar(99)
) engine=innodb

create table user_attributes
( id int not null auto_increment primary key
, user_id int not null
, attribute_type varchar(99)
, attribute varchar(99)
) engine=innodb

the user_attributes table would contain data like this


| id | user_id | attribute_type | attribute | 
| 1  |   1     |  eye colour    |   blue    |
| 2  |   1     |  hair colour   |   brown   |
| 3  |   1     |  beard colour  |   NULL    |

Question. What do you think is wrong with the code below?
This is supposed to check the database once the user is logged in and compare their session email with the email stored in the database and direct them to the appropriate page. Basically, if the field in Date of Birth (DOB) is null or empty, it directs them to the form page and if it’s not null or not empty, it’s supposed to take them to the home page.

Unfortunately, I can’t seem to get it to work that way with this script. I’ve tried empty() and is_null and even $DOB['DOB]==null but the results are never consistent – sometimes it takes them to one page and sometimes the other.

The object Getdata returns a multidimensional array with the username, password, DOB and etc. However, my query is just asking for DOB. When I do a print_r($DOB) I get an empty array with DOB=>‘the date here’ and if there’s no date in the database then array is all empty =>(). What do you think the problem is? Everything seems to be correct. All the variables have the right data in them session has the email and $DOB stores the array.

require_once $_SERVER[‘DOCUMENT_ROOT’]. ‘/video_dating/includes/Database.php’;
$db = new Database();
$useremail = $_SESSION[‘email’];
$db->Query(“select DOB from users where email=‘$useremail’”);
$DOB[‘DOB’] = $db->Getdata(); //returns md array

if (empty($DOB))
{
include ‘profile.html.php’;
exit();
}
else
{
include ‘basic.html.php’;
exit();
}