1

This question is a little open-ended and subjective, but hopefully it's still okay for me to ask on here.

I was contracted by a small business to do a project and as part of it received a database dump of their website. I was appalled when I saw the database had 542 tables in it because every attribute is stored in a separate table.

For example, there are tables like this used for identifying customers:

field_data_field_first_name
field_data_field_last_name
field_data_field_email
field_data_field_phone_number

The field_data_field_first_name table has the following structure:

entity_type
bundle
deleted
entity_id
revision_id
language
delta
field_first_name_value
field_first_name_format

That goes on for a total of 542 tables.

Is structuring a database like this a standard practice?

I'm relatively inexperienced with database design, so while I feel whoever designed this database did a rather poor job, I'd like a second opinion before approaching the company about it.

As an aside, they have an index on every column in every table, which seems bad. It probably goes without saying that the database is incredibly slow. It has an e-commerce portion and it takes between 1-2 minutes to query unfilled orders from the database.

3
  • 1
    Quick comment about performance, you should examine what wait events your queries are seeing. I seem to recall that the default engine (InnoDB) uses table level locking which ends up causing performance issues for simple activities like user logins. If you need a tool that can help with MySQL performance, check out DPA from Solarwinds (disclaimer, I work for Solarwinds) solarwinds.com/database-performance-analyzer-mysql.aspx Oct 21, 2015 at 15:04
  • @SQLRockstar MyISAM is the one using table locks (and it used to be the default engine).
    – jkavalik
    Oct 22, 2015 at 6:36
  • 542 tables? No.
    – Rick James
    Nov 3, 2015 at 15:57

1 Answer 1

1

Is structuring a database like this a standard practice?

No.

I'd like a second opinion before approaching the company about it.

Are you explaining poor performance? Complaining? Suggesting a re-design or replacement? Did anyone ask for your professional or personal opinion of the system? Is it going to affect project estimations and cause issues?

Why you are going to approach the company should form the basis of how you bring this up and what justification you need to bring to the table. If it's unsolicited advice for a small company, you might approach it more diplomatically by determining if this design issue is known, if it's causing issues, and if someone there is attached to, and proud of, their baby.

There's plenty of poorly designed systems and weird implementations out there... and while it's easy to shake our heads and second guess it's not always helpful to the client or ourselves. ;)

Not the answer you're looking for? Browse other questions tagged or ask your own question.