16 replies to this topic

[toxifyshadow]

Instead of creating many databases for different things, could I create one database and a bunch of tables for different things? (like 1 for email subscribe list, 1 for user login, etc...) My old host has a 5 database limit and uses phpMyAdmin. (if you're wonder why I said "my old host", I don't really have a host. Read My adventure to find a web host, it's a story in my blog)

[Arctic Fire]

Absolutely. That's what they're for. A common misconception that people have when they install things like a forum or a blog is that they have to create a new database for each thing they install. You can use one database for it all.

[toxifyshadow]

:) :D

Lol this is both our 26th post

[WingedPanther]

I regularly work with a database that has over 400 tables.

[DarkLordoftheMonkeys]

Most DMBSs let you create multiple tables in a single database. That's where the term "relational" comes from. Exceptions to this are called flat file databases. There is no extran knowledge you need to have to create multiple tables. You just go into the database you want (in MySQL the command would be USE my_database), then create the table using the regular syntax.

[toxifyshadow]

DarkLordoftheMonkeys said:

Most DMBSs let you create multiple tables in a single database. That's where the term "relational" comes from. Exceptions to this are called flat file databases. There is no extran knowledge you need to have to create multiple tables. You just go into the database you want (in MySQL the command would be USE my_database), then create the table using the regular syntax.

Ok, thanks for explaining relational db and flat files. I had a book on sql and looked alittle at it and it mentioned both those.
And I totally agree with your sig.

[SoN9ne]

I have a question that I feel is relevant to this post.

Does having more tables in your database affect the performance of the database? Meaning, will my database of 20 tables perform faster than my database with 400 tables? Isn't it a lighter memory footprint to switch between different tables than to just fill one database up entirely? Or is this entirely dependent on the code used to fetch and compare data?

I always figured it was safer and cleaner to move my archives into its own database instead of having it moved to a archived tabled in the same database, am I wrong? (my archives currently hold about 12 million records and growing )

I am not fully aware of the memory footprint on different database setups... time to visit Google ^^

I have never had an issue with this, I just would like to know.

[WingedPanther]

Unless you have a lousy database, having many tables shouldn't be an issue. Having many records in a table can be interesting, though.

[SoN9ne]

Thanks for your response. I think I can agree with you on this.

The system I am currently repairing was designed by someone learning while he was developing. The database is a disaster, indexes are not setup properly duplicate content in the database, I could go on with how bad the structure really is but I don't feel like typing this all out.

I personally use a single database for the live site and one for archives. Depending on certain setups, I have other databases to trigger off of depending on the users login/location (system is spread across different URLs so I can keep the majority of content in the main database while the more sensitive data is in the specified database for the user/site.). I believe my problem (not my problem but the current system I am working on) is just bad indexes and/or lack of proper indexes, not to mention the overall tables being used are not using efficient collations... The killer is the main table being used has 12 million records and the querys are so poorly structured that each query returns almost 24 million records... This is why it's important to know how to properly join tables... I love cleaning up others mistakes, make me the $$ :lol:

Anyways, thank for you response. :thumbup1:

[toxifyshadow]

wingedpanther said:

i regularly work with a database that has over 400 tables.

wtf do you do with 400+ tables??

[WingedPanther]

It supports an enterprise level application with very complex data.

[toxifyshadow]

Oh.