Database design advice

[harkly]

I have set up a database and am looking for advice on if it a good design, it is for a dating site. I am not sure if I should combine a few of my tables but not sure how many headings is to much especially when searching. The gist of my database is to collect information about the individual and track when they login.

 

 

CREATE TABLE about_me (about_me VARCHAR(32) NOT NULL PRIMARY KEY,

username VARCHAR(32) NOT NULL,
headline VARCHAR(50),
description TEXT,
attribute_1 VARCHAR(20),
attribute_2 VARCHAR(20),
attribute_3 VARCHAR(20),
wls VARCHAR(50),
wls_month TINYINT(2),
wls_day TINYINT(2),
wls_year TINYINT(4),
wls_goal_met BOOL,
status  VARCHAR(20),
kids VARCHAR(40),
Kids_want VARCHAR(40),
smoking VARCHAR(20),
smoke_mate VARCHAR(20),
alcohol VARCHAR(20),
tattoos BOOL,
pets VARCHAR(20),
education VARCHAR(20),
profession VARCHAR(20),
photo_1 VARCHAR(20),
photo_2 VARCHAR(20),
photo_3 VARCHAR(20),
photo_4 VARCHAR(20),
photo_5 VARCHAR(20));


CREATE TABLE user (username VARCHAR(32) NOT NULL PRIMARY KEY,

pswd VARCHAR(32),
email VARCHAR(50),
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(10),
birth_day TINYINT(2),
birth_month TINYINT(2),
birth_year TINYINT(4),
street VARCHAR(20),
city VARCHAR(35),
state VARCHAR(2),
zip TINYINT(5),
signup_date DATE);



CREATE TABLE general (general_id VARCHAR(32) NOT NULL PRIMARY KEY,

username VARCHAR(32) NOT NULL,
last_login TIMESTAMP NOT NULL);


CREATE TABLE appearance (appearance_id VARCHAR(32) NOT NULL PRIMARY KEY,

height VARCHAR(,
eye_color VARCHAR(5),
hair_color VARCHAR(20));


CREATE TABLE bck_values (bck_value_id VARCHAR(32) NOT NULL PRIMARY KEY,

religion VARCHAR(75),
religion_importance BOOL,
politics VARCHAR(35),
politics_importance BOOL,
ethnicity VARCHAR(35));


CREATE TABLE interests (interests_id VARCHAR(32) NOT NULL PRIMARY KEY,

sports BOOL,
exercise VARCHAR(35),
exercise_type VARCHAR(35),
outdoors BOOL,
artistic BOOL,
tv_shows  VARCHAR(75),
music VARCHAR(35),
free_time VARCHAR(150),
favorite_places VARCHAR(150),
interests VARCHAR(50),
favoritie_things VARCHAR(150));


CREATE TABLE relationship (relationship_id VARCHAR(32) NOT NULL PRIMARY KEY,

alien BOOL,
qualities VARCHAR(150),
ideal_match VARCHAR(150));

[btherl]

There's a few things I would suggest

 

1.  Use date data types whenever storing a date, so you can do comparisons in SQL easily.

2.  Store multiple items like attributes and photos in a seperate tables.  You can have a column to indicate whether it's #1 or #2 or #3.  That allows you to easily change the number of items that can be stored, and also lets you more easily do operations that affect "all photos" or "all attributes".  Eg, let's say you want everyone with attribute "strong".  In your current design, you have to check three columns to see if any matches.

3.  If there's any possibility of changing usernames in the future, you should identify users with user id (the PK of the user table) rather than user name.

[harkly]

2.  Store multiple items like attributes and photos in a seperate tables.  You can have a column to indicate whether it's #1 or #2 or #3.  That allows you to easily change the number of items that can be stored, and also lets you more easily do operations that affect "all photos" or "all attributes".  Eg, let's say you want everyone with attribute "strong".  In your current design, you have to check three columns to see if any matches.

 

So you think I should do something more like these??

 

 

CREATE TABLE photos (photoID VARCHAR(32) NOT NULL PRIMARY KEY,

userID VARCHAR(32) NOT NULL,
photo_1 VARCHAR(20),
photo_2 VARCHAR(20),
photo_3 VARCHAR(20),
photo_4 VARCHAR(20),
photo_5 VARCHAR(20));



CREATE TABLE status (statusID TINYINT(2) NOT NULL PRIMARY KEY,

status VARCHAR(16) NoT NULL);


CREATE TABLE alcohol (alcoholID TINYINT(2) NOT NULL PRIMARY KEY,

alcohol VARCHAR(16) NoT NULL);

 

I was afraid that I would have to many tables. But I can definetly break them.

[btherl]

I was thinking more like this:

 

CREATE TABLE photos (photoID VARCHAR(32) NOT NULL PRIMARY KEY,

userID VARCHAR(32) NOT NULL,
photo VARCHAR(20),
        photo_number TINYINT);

 

This is not necessarily a better design, but it might be better.  It helps if you want to renumber photos - you just need to update the photo_number column, instead of moving values around between photo_1, photo_2, and so on.  It also helps if you want to search for all photos with a particular name, as you can search for the photo column only, instead of searching 5 columns.  It also lets you change the allowed number of photos without adding columns.

 

These days I don't worry about too many tables because I'm quite good at joining tables together.  If you're not experienced with joins it can be quite tricky though.  There's no technical issue with having 100 or 500 tables, just the issues that might arise when joining many together.  It is often more efficient to include data in a table rather than have two tables and join them together.

[harkly]

I gotcha, Thanks for the advice!