seeking table organization advice

TINYTEXT is only 256 characters. That's not big enough for several of the places where you indicated use. A TEXT field is 64KB, and a MEDIUMTEXT is 16MB.

I've substituted numeric fields where you had character fields in several areas. An example is `users.approved`. You indicated 3 switches. Instead of using a character field, wich may take up to 3 bytes to store your data, consider using a single byte tinyint field. 1 byte has 8 bits. Thats 8 switches. So, byte zero = 'entire profile', byte one = 'bio', and byte two = 'pics'.

I only have one `dob` field. This is because you can store everything in one field. If you want to query only the month, you can use MySQL functions in your query to return only the month.

`user_stats.gender` can be indicated by a single characer: m,f

`user_stats.height` can be stored in one field by converting feet to inches before storing in the database. This will also make it easier to select a range later.

`user_stats.body` only needs to hold the select menu's index number, not the text.

I completely removed the 'chinese zodiac' and 'regular zodiac' because knowing the user's DOB, you can come up with it.

ICQ numbers don't fit in smallint fields. They actually take a 4 byte integer.

I've missed some other fields here and there. I either missed them, or didn't understand their use. I also didn't look over the forum tables. I'd suggest you acquire some forum software, and try to tie in your project.

MySQL Docs:
6.2 Column Types

Mark- thank you. you're the first person at any of the three forums i've found that has people that do thisstuff to respond. also this happens to be the last one i found.

Quote: I've missed some other fields here and there. I either missed them, or didn't understand their use. I also didn't look over the forum tables. I'd suggest you acquire some forum software, and try to tie in your project

i've updated mocst of them based on what you said because i agree, i'm looking at the others you've switched to ints to use index numbers and am wondering on some if it might help you to see the sign up preview on the webspace i'll be losing at the end of july where which i've been using to create possible design and such based on what those i'm working with have been telling me.

we're going to use one of the versions of the preview 6 set of profiles. we haven't decided which one because everyone has a different idea for where the rating bar should go and we haven't had a chance to sit down and talk it over, but here's the profile as of now:
http://people.brandeis.edu/~m3rajk/JMT/preview/preview-profile6a.html
and here's the sign up with some debugging stuff as we're trying to get it to validate. it loses step when you get to step 4. for some reason it's not being passed. that has been happeneing since i added the max upload size (we haven't test uploads because we're waiting to get the validation to work. we're not sure we'd be able to on there).
http://people.brandeis.edu/~m3rajk/JMT/preview/phpprev/signup.php

the reason we're not downloading phpbb invasion or one of the other boards is because this is an attempt to show off what i was able to do with php/mysql. so i want the entire thing to be my creation.

getting the forums up is secondary to getting the profiles working. we also haven't talked much about the messaging system. we all agree we want one, but one likes the version with mail and pop up alerts, the other person i'm working with like seperate mail and "instant messages" that pop up. they both have advantages and disadvantages in practice. i'm leaning toward the mail and pop ups since i think it would be easier. but the priority we decided on was profiles, forums, messaging system. so i've put the messaging system off and started looking at forums since i've seen they are definitely the lifeblood of a site

Quote: Originally posted by m3rajk
i'm looking at the others you've switched to ints to use index numbers and am wondering...

Which fields did you have questions on?

there's a couple you have varchar(50) when i know at least one i use html to limit it to 100 characters.

i was wondering why the varchar is set to 50 there. and for weight i noticed you have tinyint(3) unsigned. even though i doubt anyone weighing over 1000 lbs would want to be on a site like this, since it is possible to have a 4 digit weight, should it not be tinyint(4) unsigned?

In the case of integer fields, the number in parenthesis does not effect the capacity of the field. That number simply indicates the maximum display size, which is really ignored unless you also define the field as ZEROFILL.

A tinyint is 1 byte. Unsigned, thats 256 max.
A smallint is 2 bytes. Unsigned, 65535.
(mediumint = 3 bytes, int = 4 bytes, and bigint = 8 bytes)

I changed `user_stats.city` from varchar(100) to varchar(50) because really, how many city names do not fit in 50 chars?

I changed `user_stats.religion` from varchar(100) to smallint(50) because I figured a select box would suffice for a list of religions. A 2 byte integer field will allow you to store the index of up to 65,535 different religions.

I noticed on your form, that you have a select box for hair color but not eye color. Why not for eye color? You could store that in an integer field as well. If you would settle for a 265 color selection, you could store the index in a tinyint field. That's 1 byte opposed to a possible 10 bytes with varchar(10).

I didn't change state/province to a integer field because I doubt everyone will live within the US. And allowing them to enter the text manually is easier than acquiring a list of all states/provinces world-wide.

I changed the tinytext fields in `user_interests` to text fields because tinytext is only 256 bytes, whereas text is 65,535 bytes.

when i was writing it up a friend of mine linked me to a johnson & johnson page asking me which color contacts she should get. i figured most of them can be abbreviated.. and there was a wider selection than i'd have ever considered. but yeah, i see the point with eye color.

well. i found the dates for regular zodiac. now i need to know the order for chinese zodiac. i know 79 was ram and 74 was tiger, and it's a 12 yr cycle, but that's all.once i know them all, the order and have a year to use as a calculating opint (hmmm... 1979?) then i can create a function for it like i did regular zodiac...

function findRZodiac($month, $day){ #finds regular zodiac
if((($month==1)&&($day>19))||($month==2)&&($day<19)){
return 'Aquarius'; # January 20 - February 18
}elseif((($month==2)&&($day>18))||($month==3)&&($day<21)){
return 'Pisces'; # February 19 - March 20
}elseif((($month==3)&&($day>20))||($month==4)&&($day<20)){
return 'Aries'; # March 21 - April 19
}elseif((($month==4)&&($day>19))||($month==5)&&($day<21)){
return 'Taurus'; # April 20 - May 20
}elseif((($month==5)&&($day>20))||($month==6)&&($day<22)){
return 'Gemini'; # May 21 - June 21
}elseif((($month==6)&&($day>21))||($month==7)&&($day<23)){
return 'Cancer'; # June 22 -July 22
}elseif((($month==7)&&($day>22))||($month==8)&&($day<23)){
return 'Leo'; # July 23 - August 22
}elseif((($month==8)&&($day>22))||($month==9)&&($day<23)){
return 'Virgo'; # August 23 - September 22
}elseif((($month==9)&&($day>22))||($month==10)&&($day<23)){
return 'Libra'; # September 23-October 22
}elseif((($month==10)&&($day>22))||($month==11)&&($day<22)){
return 'Scorpio'; # October 23 - November 21
}elseif((($month==11)&&($day>21))||($month==12)&&($day<22)){
return 'Sagittarius'; # November 22 - December 21
}elseif((($month==12)&&($day>21))||($month==1)&&($day<20)){
return 'Capricorn'; # December 22 -January 19
}
}

1900 works better since it's the earliest choice. infact, all i need then is to subtract 1900 from the year entered and then mod by 12.

only question is, how can i set the year to "please select" or something like that when it's not chosen while using the forloop i have? this function gets the chinese zodiac perfectly:

function findCZodiac($doby){
$sign=($doby-1900)%12; # $doby (user's year of birth) - 1900 (earliest year choosable) mod 12 (# of signs)
if($sign==0){ return 'Rat'; }
elseif($sign==1){ return 'Ox'; }
elseif($sign==2){ return 'Tiger'; }
elseif($sign==3){ return 'Rabbit'; }
elseif($sign==4){ return 'Dragon'; }
elseif($sign==5){ return 'Snake'; }
elseif($sign==6){ return 'Horse'; }
elseif($sign==7){ return 'Ram'; }
elseif($sign==8){ return 'Monkey'; }
elseif($sign==9){ return 'Rooster'; }
elseif($sign==10){ return 'Dog'; }
elseif($sign==11){ return 'Pig'; }
}