大型和可扩展应用程序的数据库表结构

Iam a software engineer (since a few months ready with my study) and for my work i develop a large scalable web application. Another firm does the programming work and makes the database behind it. We defined the data and the relations between them but didn't give a hard database structure they should use.

Now the first (internal) things are visable. I looked in the database structure behind ans saw (in my opinion) something weirds.

For users they created a table users which contains stuff like id, email and password. Near this they created an user_meta table which contains an id, key and value.

When i have a user with:

  • userid
  • email
  • password
  • name
  • lastname
  • address
  • phone

the id, email and password is stored in the user table. For the other information are rows created in the user_meta table. This means in this case there are 4 rows created for 1 user (in our case are this more than 20 rows for each user) This structure is user for every object what should be saved in the database.

I learned to create a table which contains all the data which is necessary for the user. So in my logic it should be one table with 7 fields...

Extra information:

  • Our software is build on the Laravel framework (on my advice)
  • Our software uses MySQL database

Questions:

  • Is this a normal way to create database for large systems or something like that, becouse i never see this in my study or other projects in my live.
  • Is this best or bad practice?
  • In my opinion this is bad for performance. Especially when the user count grows, is this true? or can this be done to get the performance high (becouse not a whole record needs to be selected ?(in our case we expect AT LEAST 100.000 users at the end of 2017 which wil grow faster and faster when our project passes. there is a material chance that we grow far above 1.000.000 users in a few years)
  • I think the reason why they do it like this is that the "objects" can be changed very easy but in my opinion is it allways posible to add fields to an table and you should NEVER delete fields (also not when it is posible by the structure of the database), is my opinion in this right?

sorry when the questions are "noob questions" but for me this is the first big project in my live so i miss some experience but try to manage the project professionally. We will discuss this wednesday in an meeting. On this way i want to prepare my self a bit in this theme.

In my opinion this is bad for performance. Especially when the user count grows, is this true?

No.

There is a small difference in the insert/update cost which will not be dependent on the volume of data previously accumulated. Retrieval cost for a full record will be higher but slightly reduced for a partial record. At the end of the day, the performance differences are negligible as long as the record is still resolved in a single round trip to the DB (unlike a lot of trivial ORM implementations).

The biggest impact is functional. Its no effort to add, say, an attribute for title to the EAV table, but the normalized table may be knocked offline while the table is stresteched to accomodate the wider records (or the rows are migrated). OTOH you can't enforce a constraint at the database tier like every customer must have an email address.

Is this best or bad practice

Of itself neither. Although not documenting design decisions is bad practice.

far above 1.000.000 users in a few years

a million records is not a lot (unless the database design is really bad).