Forum Moderators: phranque
I just set up my first fully db-driven website using PHP and mySQL. I must say that I'm impressed about the possibilities that I have now.
While working on the page I came across many mySQL tutorials that were dealing with database design. However I had trouble implementing these principles in my project.
So I was wondering what your experiences with database design is. What principles do you follow when you set a database up?
Trying to set up as many tables as possible?
Trying to limit number of records per table?
tks
Dan
devshed.com/Server_Side/MySQL/Normal/Normal1/page1.html
www.phpbuilder.com/columns/barry20000731.php3
The more time you spend at the beginning hte better. Get it right and things flow, get it wrong and you will be tinkering (if not total redesign) for ages.
Cheers
Try doing it at university... they spend 2 years telling you that it's magic, only to eventually find out it's all just common sense in action.
SN
Always name databases, tables, and fields appropriately and sensibly. When you come back to a project months later you will thank yourself. (Don't use field names like cba1 for customer billing address 1, try "billaddress1" or something).
Always try to use the same name for the index field. Typically a table will have an integer auto_increment field in position 1. If you always name it "idx" or something, life will be easier for you down the road.
Always specify field names in your INSERTS. If you follow the practice of "INSERT INTO table VALUES (NULL,'a','b','c','d','e')" instead of "INSERT INTO table (idx,ac,bc,cc,dc,ec) VALUES (NULL,"a","b","c","d","e")" - when you modify the table structure you might have just broken 50 scripts. If you specify all the field names (even if there are 200) you will suffer much less pain over all.
Always try to use the same name for the "timestamp" field. Most of the time I care about when a record was added to a table, so I always stick a DATETIME field "sequence" in the last position. "INSERT INTO table (idx,fc,sequence) VALUES (NULL,'foo',NOW())". You can also use a real TIMESTAMP field, however I have grown to prefer DATETIME.
In your PHP code it may be helpful to place variables outside of the sql string. I always follow this convention: $sql = "INSERT INTO TABLE (idx,fc,sequence) VALUES (NULL,'".$variable."',NOW())";
This makes for more typing, and may not look as pretty, but if you have a syntax highlighting editor, your mistakes will be more easily identifiable. Typically a syntax highlighting editor will disregard variables and code inside quotes.
Always use mysql_escape_string or add_slashes on data you collect from your visitors and stick in the database.
$name = add_slashes($_POST['name']);
FYI, A quick way to stuff everything into a table would be something like this: $sql = "INSERT INTO postdata (idx,serverdata,postdata,getdata,sequence) VALUES (NULL,'".base64_encode(serialize($_SERVER))."','".base64_encode(serialize($_POST))."','".base64_encode(serialize($_GET)),"',NOW())";
Then you can magically pop it out back into arrays. Makes for difficult reading in something like phpMyAdmin, though.
The best advice I have is to figure out what you like, and keep consistent. This will save you time in the future.
Take Care,
Make sure your my.cnf allows enough concurrent connections, else your site will be unavailable when you reach the limit.
Best,
I have a question about the my.cnf file.
I'm running mySql on Windows and couldn't change that file to increase number of connections. Was there other stuff to do besides changing that file?
thanks again!
P.s. That my.cnf file thing sounds interesting... actually I never thought of it that the connections to a database might be limited. I'll have to look into that.
The default is 100. When I first started using mysql years ago, I overlooked this. Eventually traffic demanded more than 100 simultaneous connections. Many visitors would receive an error message.
I upped the max connections to 1024, and increased some other values
(in my.cnf)
[mysqld]
set-variable = max_connections=1024
set-variable = key_buffer_size=16777200
set-variable = back_log=256
set-variable = table_cache=256
instead of taking the time to explain all that stuff here, you should probably read the information on the mysql web site.
[mysql.com...]
btw, if you are running mysql on a windows machine (and not using cygwin to run it), try using the winmysqladmin.exe program in c:\mysql\bin (or wherever you installed it).
i think it will give you a hand setting the variables.
make sure to restart the mysql process after changing my.cnf.
take care,
The link to the reference was good, but it still didn't explain how to change everything to get it to work properly.
I'd love to learn how to do this. I have mySql installed on another server - maybe I can 'break' that one for the learning process.
But you are right, 100 connections will be eaten up rather quickly.
Edit: Well I guess I answered my own question. I added your four vairables to my "my.ini" file and restarted the service and all seems to be working. The winmysqladmin does reflect the 1024 max connections.
Thanks.
in that installation directory there are three or four example my.cnf files.
the actual file used to set the mysql configuration is located at C:\WINNT\my.ini
you will need to restart the service (or reboot the machine) to get the changes to take effect.
hope that helps!
