MySQL tables and databases?
When creating a database in MySQL for a large website that will possibly hold vast amounts of data, is it possible to have the website work from more than one database and still function properly?
If it is possible, would doing so be wise?
Also, regarding tables. Is it possible to create tables inside of tables (almost like sub-tables) and if so how? Or is just adding columns/creating additional tables just as effective to serve this purpose? Say for a large amount of user data, I figure I may need to split much of the collected data into separate/sub tables-- but if that doesn't work, what is the best way to use MySQL to keep all users data in-line and quickly updated?
All help is appreciated and thanks in advance.
Answers & Comments
Verified answer
There is nothing wrong with using multiple databases' as long as you ensure you are separating the data properly. I.E. -> If you use a CMS, that CMS should function from 1 independent DB. You can have other programs accessing that DB too.
If you plan to run full query's on all tables in a DB -- for specific functions, like 'On-Site Search', or something to that sort - then you'll want to ensure that it has it's own physical DB.
You don't want to be cycling through 10,000 tables to change / edit / add data.
I don't see any reason you'd ever have to create a table inside of a table. That's not how MySQL works.
imagine the MySQL Table just like a table in HTML>
<table> - we define the name of our table here
<th> - column name, like ID.</th>
<th> - column name, like "name". </th>
<th> - column name, like "phone". </th>
<th> - column name, like "email". </th>
<tr> - ID#1 Data ---
<td> 1 </td>
<td> george </td>
<td> 555-5555 </td>
<td> [email protected] </td>
</tr>
<tr> ID#2 Data---
<td> 2 </td>
<td> Jane </td>
<td> 555-4444 </td>
<td> [email protected] </td>
</tr>
</table>
You can see here how the data is structuring its self. a 'row' is a new entry, always. You can create an unlimited amount of values within this row.
such as (pickin up from jane)
<tr>
<td> 2 </td>
<td> jane </td>
<td> 555-4444 </td>
<td> [email protected], [email protected], [email protected], [email protected], [email protected] </td>
</tr>
you can see here, that we didn't need to create another table to store values. Instead, MySQL Supports "Comma Separated Values".
You can access ONLY certain values from within the MySQL database by specifying tit through the query.
("SELECT * FROM My_table where email='jane@*'");
Here we're telling the query to return jane@ anything
we can change that, so
("SELECT * FROM My_table where email="*@hotmail.com");
Here, we're telling the query to return any values that end with @hotmail.com
Since Jane is the only entry with @hotmail, we wno't se george, just jane.
we can also.
("SELECT * FROM My_table where email="*@*.*");
Here, we will return any values that match [email protected]
("SELECT * FROM My_table where email='"*@*.com");
Here, we will return any values that match [email protected]
Hope this information was helpful.