Many-to-many relationship

I have two tables, customers and bank accounts, with a many-to-many relationship (each user can have more than one account and an account can belong to multiple users). How开发者_开发问答ever, some of the attributes of the table, such as email in the customers table, are empty (most customers don't have an email). How can I fix this? Do I create a new table?

While creating sparse columns will not corrupt your database, I would advise against it. First, allowing nulls violates the higher-normal forms and should be avoided for data-integrity reasons.

Second, a customer may have multiple email addresses, so email address are not "attributes" of a customer in relational algebra and cannot be handled by a single Customer column.

I suggest creating a separate Email table, with a unique primary key and customerID as non-unique foreign key. Add as many Email records as a customer has emails.

No. In the table customers let the field null or 0. If a customer would has more than one e-mail, create a new table "emails" and link it to "customers" table in a one-to-many relationship.

There's nothing wrong with having a database column consisting of mostly NULL values. It's called a sparse column.

You could put the optional column in a separate database table. If the overhead of additional keys and a primary index is less than the amount of storage "wasted" by all of the NULL values, you could even use less storage space. It's not likely, since modern databases optimize sparse columns to use minimal storage space.

You would have to define a separate database table for each sparse column in your customer table and / or bank account table.