If you answer, you're PHP MASTER

[TheFilmGod]

I'm trying to build a mysql database that may have up to millions (in the far far future) number of tables upon tables. I want to make it efficient and work fast. How do I design a good mysql schema that will work effeciently, fast, and easy to maintain along side php? If  you know a link to a good tutorial please post!!

 

Thanks for help in advance! (The resources listed under php freaks about mysql organization is NO HELP).

[Caesar]

Millions of Tables? Or millions of records? If we're talking tables...I would not recommend MySQL.

[TheFilmGod]

Okay. I didn't mean "millions" I was being sarcastic. Maybe like a few thousand tables. - The idea isn't about how many tables, its about the actual design. (The way the tables are structured).

[virtuexru]

You mean records.. As far as tables, you shouldn't have many. If you are going to have millions of table (not records), then you need another database system

[TheFilmGod]

So its better to have few tables that have a lot of records, then a lot of tables and a few records?

[Caesar]

So its better to have few tables that have a lot of records, then a lot of tables and a few records?

 

Yes.

[virtuexru]

Yea, the point of a table is to organize and be able to sort through millions of records of data.

[TheFilmGod]

Thanks a lot. Its all starting to make sense.

 

I'd like to follow up with another question. Let's say I'm making a site like facebook. And there is information on each person. Is it the best idea to make a HUGE table with everbody's information. Or is it better to create a few tables, each with a cateogry. Like a table for general information and one for "favortie friends." Which solution is better? A huge table or a few tables, a table for a category?

 

Don't worry, I'm not actually going to create this! And mysql wouldn't be able to handle that much of an overload. I'm just giving an example.

[trq]

And mysql wouldn't be able to handle that much of an overload.

 

What makes you the expert? Sounds to me like you need an understanding of database design and normalization techniques. This page may get you started.

[rtpmatt]

It depends on what the data looks like.  You would probably have a main table to include information that every user will have like name, login, password, location, sex, etc.

 

then for other dynamically sized datas such as number of friends, messages, bullitins, etc, you would probably use other tables.

 

does that make sense to anybody?

[cooldude832]

A Table should contain all data that can have the same fields like users, or advertisments, logs, etc etc you shouldn't make a table for each user you should make a entry in the table users for each user.  Only way you need a bunch of tables is if some how each user had a way/need for a customized table

[teng84]

millions of records??? use oracle

and to normalize ur table and save save space use id instead of usinf the whole word

 

say absent, late , ontime insread of using the whole word u can have just the id to serve as identifier.

  now look  late , ontime insread  this is lonfg enough to understand what i mean now if u use their id it will only look like this 1, 2, 3 see 3 characters are only store in your db

[cooldude832]

i'm not claiming to be a php expert at all, but my understanding is that while mysql can hold large volumes of data it is not intended to have millions of entries.  A table can have lots of data (blob,text can hold flat files in binary).  But for million of entries its not meant for that because the primary use of mysql is a fetch method, not a echo out an entry.  More corperate style db like oracle would probably be best for your system.  You might be able to get away with flat filing and mysql if you have a powerful server and layout your organization in a good way.

[per1os]

Normalization is the key.

 

You want your tables to be in 3NF form. MySQL will work just as well as Oracle if configured properly.

 

Basically here is how a design for let's say friends would work:

 

create table users( 
userid int(11) NOT NULL auto_increment,
username varchar(50) NOT NULL,
firstname varchar(30) NULL,
lastname varchar(50) NULL,
password varchar(50) NOT NULL,
email varchar(250) NOT NULL,
Primary key(userid)
);

Create table friends (
friendid int(11) NOT NULL auto_increment,
userid int(11) NOT NULL,
frienduserid int(11) NOT NULL,
active tinyint(1) NOT NULL default '1',
Primary Key (friendid)
);

 

That is an example of 3rd normal form. That is the best way to really structure tables in a sense. This is a zero to many relationship between friends and users.

 

There is no need for a ton of tables, just tables that are necessary, in this case friends table is necessary as there can be multiple friends for a user. But to store the users personal information in a seperate table would no be necessary as really you will only need one record per user.

 

And for thousands of records, MySQL works great. I have over 200,000 records in my database including templates, blogs comments, users etc. And my site runs just as smooth as it did with 1 record. The database is over 250MB's in size to give you an idea.  MySQL is very efficient.

[ToonMariner]

From memory MySQL can handle any size of database - its the table size that is important - 4G is (from what I remember) the point where things go boobies up.

 

Its the use of resources that are paramount.  provided you apply the normalization well then all should be dandy.  If you know your site WILL get bigger and bigger I'd go as far as to normalize the names of people!!!!

 

Although I have rarely used oracle I would concede it is much more robust and has some very useful functions that you can't directly replicate in mysql.  That said I believe MySQL is more than capable of providing the efficient resource 99.99999% of websites would ever need...

[neel_basu]

So its better to have few tables that have a lot of records, then a lot of tables and a few records?

 

Yes.

No no.

it depends upon what type of data you are inserting into tables.

If you are doing something thats Pluggable You should use Separate Pluggable Tables.

Suppose You have a Shop That has a Main Brunch and many sub brunches.

You should make separate tables fro the Sub brunches. That are Connected by a Primary key like shop_id.

As you are handling 1000s of records you shouldnt have more than 8-10 Columns in 1 Table. If some Columns are interrelated make a separate table for them.

According to DBMS you should do this for Maximum performance.

[TheFilmGod]

Wow. Thanks for everyone who replied. I see this came down to a very HEATED debate  .

 

Some keypoints I'd like to highlight: Better to make a table for each category. A table for general information, one for login, another for friends, etc. Another thing is to use id keys for repeated terms. So disk space is conserved. Lastly, NORMALISING the mysql.

 

I'm still a bit confused but it def. helped me a lot!!!

 

I'd like to follow up with a question. My host says my MYSQL limitation is:

 

We allow up to 50 simultaneous connections per MySQL user.

 

What do you think this means? I don't want to overload the MYSQL.

[teng84]

so find allot of php master ?? funny great idea many answer the topic

[TheFilmGod]

so find allot of php master ?? funny great idea many answer the topic

 

LOL!  Its sorta like forum advertising! Why should someone reply to YOUR topic? Unless it makes them a php master!

 

Yeah, I found a lot of 'php masters'. Everyone pretty much. But there were some ppl I know were completely wrong...

 

Anyone know what 50 simultaneous connections per mysql user means?

[redarrow]

It means your only allowed to use 50 database's but within

all those 50 databases you can have many tables and colums/fields.

 

The new version off mysql outways many other databases.

I have used them all and still say mysql is the best.

[TheFilmGod]

Thanks. But on the c-panel for my hosting package it says: 1 of 10 mysql database setup. 9 more still not setup.

 

Its all confusing me! ???

 

I was thinking that it means that at anytime, I can have 50 ppl on my website and they can connect to the mysql and query it without trouble. But if more ppl need to connect, than there would be a waiting time. - a  delay.

 

I guess my host does this so I upgrade. By upgrading I increase the '50' to '100' or more...

[per1os]

It means that at any point in time you can have a maximum of 50 open connections to a database.

 

Usually 50 is plenty, most hosts default to 25 I think or less. Basically a connection opens when a page starts to load and closes after the page is done, so it is really only open for around .5 seconds or less depending on how efficient your site is.

 

50 will be good until you start to see a "Too Many Connection" problems, thats when you bump up to 100.

[TheFilmGod]

Omg! Thanks soo much frost!! You are a life saver. I finally understand what it means! Even my support doesn't know. 

 

I'd like to follow up with a question.... Can mysql hold a good amount of requests at a time. I'm currently on a shared server. Will that create any problems for the mysql?

[per1os]

Shared servers are risky to begin with. If you have the funds and the means a dedicated is the way to go (cheap cheap end $1200/year). I love my dedicated server as you have full control over the number of connections to mysql, you know how many domains are on there and they are only domains that you allow.

 

But yea you should be fine with shared hosting, but generally they are just less secure, although a lot cheaper.

[ToonMariner]

the way to avoid exceeding your maximum number of connections is to not use persistent connections. the OOP mysqli is fabulous and while creating a connection uses a lot of resources the new imporved mysqli effort is not bad at all...

 

Try to optimize your database by index all fields that you will search on... and keep it tidy!

 

Good luck and have fun dealing with the frustration that sest in sometimes when you are trying to do something great and it just won't bloody play ball (then you realise that you have a closing brace in the wrong place or something else fundamental).