MySQL database structure optimization

Question

In my DB project for website which will be created using PHP I have round 60 tables with 2 to 6 columns in each. Some of them can have thousands records, some hundreds and some only a few (1-10). These tables where are only a few records are the one that describe user type or city size from where later in other table is only reference to this. The bigger tables are for example the user contact data info.

What I guess is also important in this architecture is that the tables are created the way that only inserts are complex because for example user insert (creation) is operation that is inserting values to 6 tables. But all updates or queries are done generally on max 3 tables.

With such big database I am wondering is it better to stay with smaller tables and during query make it more complex call, or create fewer tables with bigger number of columns?

If staying with smaller tables is better then should I use JOIN, create some VIEW, or maybe something else?

I hope I've explained completely my concerns and how database looks like.

Thank you for answers in advance.

Vikas Rana · Accepted Answer · 2014-03-26 08:05:09Z

1

In my view you should stay with smaller tables. because it is easy to maintain data in them. Also Normalise your database. save reference in your master Tables. Use foreign keys to make relationships between tables.
Than use joins to fetch data you want its very easy. I always follow this approach. and one thing Only You Know What is best suitable for you .

answered Mar 26, 2014 at 8:05

Vikas Rana

1,9992 gold badges32 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL database structure optimization

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related