0

I'm new in PostgreSQL(still learning) I'm trying to create a relational database for a venue. In my table(still in UNF) I have attribute to store the client's name, phone, email. The problem is that the client will give maybe 2 or 1 info on him. So I will always have null values. Sometimes I can get all the client's values(for the 3 attribute) How am i supposed to deal with this in the normalization process? Do I need to separate the tables in other relation. If so 3 relations is not too much?

Kavi
  • 113
  • 1
  • 8
  • Just as many as it takes. You could generalize "phone" and "email" to "communication" and distinguish by "protocol" ("mailto:" and "tel:"). In any case normalizing will allow you to store "work phone", "mobile phone", etc. without any changes to your schema. – Filburt Nov 20 '16 at 18:35

1 Answers1

2

For every attribute that should be there once, use a column in the main table. "Should" indicates it might be missing / unknown, too. That's a NULL value then. If the attribute must be there, define the column NOT NULL.

Attributes where there can be multiple distinct instances, especially if the maximum number is uncertain, create a separate table in a one-to-many relationship.

Store (non-trivial) attributes that can be used in many rows of the main table, in a separate table in a many-to-one relationship.

And attributes that can be linked multiple times on either side are best implemented in a many-to-many relationship.

Referential integrity is enforced with foreign key constraints.

It's not nearly as complex as reality, but the point is to establish a logically valid model that can keep up with reality.

Read basics about database normalization.

Detailed code example with explanation and links for n:m relationship:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Re "Store (non-trivial) attributes that can be used in many rows of the main table, in a separate table in a many-to-one relationship." This isn't clear. I guess by non-trivial attributes you mean something like, those whose representations by the DBMS are costly in terms of space and/or comparisons? And the many-to-one relationship is with an introduced trivial id that is used in the main & separate tables? – philipxy Nov 20 '16 at 21:18
  • @philipxy: Yes, basically. But there are more considerations. Is it just a *value* or the name of a separate entity? You wouldn't create a lookup table for first names, but you might do that for countries. Whole books have been written about database normalization. And there is wiggle room between theory and practice. It depends on requirements. – Erwin Brandstetter Nov 21 '16 at 02:49