Building a database system for my local Medical Association. What we have is a list with something like 70 fields of information for each member of the association. Stuff like name, surname, home address, office address, phone numbers, specialty many small details.

At the moment i've built one table with all the information related to the docs other tables for related stuff like subscription payments, requests, penalties etc.

I'm quite new to database design, and while it works, I find my design ugly. It is logical, as all information in each row is unique and belongs to just one person, but i'm sure there would be a better way to do it.

How would you go for it? Should I do multiple 1:1 tables, 1 for each subject (basic info, contact info, education, etc) or just keep it as it is? One table with 70 columns.

CodePudding user response：

I wouldn't worry about 70 columns in a table. This is not a problem for MySQL.

MySQL can support many more columns. InnoDB's hard limit on the number of columns in a table is 1000. Read this blog about Understanding the Maximum Number of Columns in a MySQL Table for details.

It's more convenient to put all the attributes into a table that belong with that table. It will take more coding work to support separating columns into multiple tables if you feel you need to do that.

If some of the columns are not applicable, use NULL. NULL takes almost no storage in MySQL, so you won't be "wasting" any space by having a lot of columns most of which are NULL.

The only downside is that you may find yourself adding more columns as time goes on, and that could be inconvenient if the table grows large and access to the table is blocked while you are altering it. In that case, learn to use pt-online-schema-change, a free tool that allows you to alter tables while continuing to use them.

CodePudding user response：

1:1 is rarely wise. But it may be advisable if you have "too many" columns, especially if they are "too bulky".

Do use suitable datatypes for the columns -- ints, dates, etc.

Do use suitable VARCHAR sizes, not blindly VARCHAR(255) or TEXT. (This will help later in certain subtle limits and in performance.)

Study the data. If, for example, only half the persons have a "subscription", then the 5 columns relating to a subscription can (should) be moved to a separate table. Probably the subscription table would have a person_id for linking to the main table. This is potentially 1:many (one person to many subscriptions) if that is relevant.

By splitting off some columns, you avoid lots of NULLs. Nulls are not harmful; it just seems sloppy if there are lots of nulls.

If you are talking about only a few hundred rows, then you are unlikely to encounter significant performance issues regardless of how you structure the tables.

"phone numbers" used to come in "home" and "work". Now there is "fax", "cell", "emergency contact", and even multiple numbers of any category. That is very likely a 1-person-to-many-numbers.

Selectively "normalize" the data. You said "local". It may be worth "normalizing" (city, state, zip) into a separate table. Or it may not be worth the effort. I argue that you should no normalize phone numbers.

Do not have an "array" of things splayed across columns. Definitely use a separate table when such occurs.

Do think about 1:1, 1:many, and many:many when building "entity" tables and their "relationships".

If you have a million rows, these tips become more important and need to be pondered carefully.