CODEWITHSUNDEEP
phpmysqldatabaseperformance
user683812
user683812

Reputation: 31

MySQL many tables or few tables

I'm building a very large website currently it uses around 13 tables and by the time it's done it should be about 20.

I came up with an idea to change the preferences table to use ID, Key, Value instead of many columns however I have recently thought I could also store other data inside the table.

Would it be efficient / smart to store almost everything in one table?

Edit: Here is some more information. I am building a social network that may end up with thousands of users. MySQL cluster will be used when the site is launched for now I am testing using a development VPS however everything will be moved to a dedicated server before launch. I know barely anything about NDB so this should be fun :)

Upvotes: 2

Views: 180

Answers (7)

Raffael
Raffael

Reputation: 20045

the simple answer is that 20 tables won't make it a big DB and MySQL won't need any optimization for that. So focus on clean DB structures and normalization instead.

Upvotes: 0

Gabriel Spiteri
Gabriel Spiteri

Reputation: 4978

I think 20 tables in a project is not a lot. I do see your point and interest in using EAV but I don't think it's necessary. I would stick to tables in 3NF with proper FK relationships etc and you should be OK :)

Upvotes: 0

Ryan
Ryan

Reputation: 1888

I would definitely not do this. Basically, the reason being if you have a large set of data stored in a single table you will see performance issues pretty fast when constantly querying the same table. Then think about the joins and complexity of queries you're going to need (depending on your site)... not a task I would personally like to undertake.

With using multiple tables it splits the data into smaller sets and the resources required for the query are lower and as an extra bonus it's easier to program!

There are some applications for doing this but they are rare, more or less if you have a large table with a ton of columns and most aren't going to have a value.

I hope this helps :-)

Upvotes: 0

Jaydee
Jaydee

Reputation: 4158

I would first say that 20 tables is not a lot.

In general (it's hard to say from the limited info you give) the key-value model is not as efficient speed wise, though it can be more efficient space wise.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425733

This model is called EAV (entity-attribute-value)

It is usable for some scenarios, however, it's less efficient due to larger records, larger number or joins and impossibility to create composite indexes on multiple attributes.

Basically, it's used when entities have lots of attributes which are extremely sparse (rarely filled) and/or cannot be predicted at design time, like user tags, custom fields etc.

Upvotes: 1

genesis
genesis

Reputation: 50982

nope. preferences should be stored as-they-are (in users table) for example private messages can't be stored in users table ...

you don't have to think about joining different tables ...

Upvotes: 0

Pwnna
Pwnna

Reputation: 9538

Granted I don't know too much about large database designs, but from what i've seen, even extremely large applications store their things is a very small amount of tables (20GB per table).

For me, i would rather have more info in 1 table as it means that data is not littered everywhere, and that I don't have to perform operations on multiple tables. Though 1 table also means messy (usually for me, each object would have it's on table, and an object is something you have in your application logic, like a User class, or a BlogPost class)

I guess what i'm trying to say is that do whatever makes sense. Don't put information on the same thing in 2 different table, and don't put information of 2 things in 1 table. Stick with 1 table only describes a certain object (this is very difficult to explain, but if you do object oriented, you should understand.)

Upvotes: 0

Related Questions