ASP.NET DATA NORMALIZATION day two

First Normal Form (FNF): This rule states that a column cannot contain multiple values. If you further inspect t_bands for FNF compliance, you should come to the conclusion that the albums and members fields, band_albums and band_members, should be broken down into smaller, discrete elements. The band_members and band_albums columns are currently defined such that if a band has multiple members or have released multiple albums, then band_members and band_albums columns will contain multiple values.

Second Normal Form (SNF): This rule states that every non-key column must depend on the entire key, not just the primary key. Because you are using band_id as your primary key, you are in good shape with respect to SNF.

Third Normal Form (TNF): This rule is very similar to the SNF rule and states thatall nonkey columns must not depend on any other nonkey columns. A table must also comply with SNF to be in TNF. OK, you pass this test too!


There are three other normalization rules that aren’t covered here. Generally, if your tables are in Third Normal Form, they probably conform to the other rules.

To fully optimize your tables, you should take some additional measures. It’s a good idea to break your t_bands table into several tables and link them to t_bands via foreign keys.

Also, you should create a t_music_types table that holds all the possible music types. The t_bands table should have a foreign key to the primary key of the t_music_types table.

This is generally good practice for two reasons: (1) it ensures that your band’s music type falls into the music type domain and (2) it is easier to maintain. For example, if you change your mind and want to refer to “R&B” as “Rhythm & Blues,” you won’t have to change every instance of “R&B” in the band_music_type_title column—you only need to change the music type title in the t_music_types table. You could also do the same thing for the band_record_company_title and contact_business_state fields.

At this point, your database contains three tables: (1) t_bands, (2) t_music_types, and (3) t_record_companies. Figure 3-2 shows a diagram of our new database design:

In the diagram, t_bands is linked to t_music_types via a foreign key to music_type_id and linked to t_record_companies via a foreign key to record_company_id.

This new relationship between the tables is called one-to-many. In a one-to-many relationship,
each entry in the contact type table may be referenced by one or many contacts.

You now have three tables and have met your current requirements. However, what about bands and albums? Currently, you are storing all of the band’s albums and members in a single
column, band_albums and band_members, respectively. Currently, if you wanted to retrieve a list of a band’s members or albums, you would need to retrieve the data in the band_members or band_albums column and parse it. This is not the optimal approach.


The best approach for this situation is to further normalize your database by creating two new tables. The first is a table to store all albums (for example, t_albums) and a second that stores all band members (for example, t_band_members). The tables t_albums and t_band_members will have foreign keys to the t_bands table. Figure shows the new database diagram.

You could certainly modify your table design further. But at some point you need to start considering performance. Performance can be adversely impacted if, on a regular basis, you need to join multiple tables with a lot of data. We recommend that you keep the number of tables in your database to a minimum while following the normalization rules as closely as possible. You will soon learn that database design is as much art as it is science.
26.47
RELATED POST

VISUAL STUDIO INTRODUCTION

C SHARP INTRODUCTION

C SHARP OUT LOOK

DOT NET AND C SHARP

C SHARP APPLICATION STRICTURE

OOPS INTRODUCTION

OOPS AND C SHARP

IDE AND C SHARP

INSTANTIATING OBJECTS IN C SHARP

CLASSES AND OBJECTS IN C SHARP

OPERATORS IN C SHARP

SWITCH AND ITERATION IN C SHARP

BRANCHING IN C SHARP

CONSTANTS AND STRING