1. Identify the entities.
2. Identify the primary keys for each entity table.
3. Eliminate repeating groups in individual tables.
4. Remove the possibility of INSERT, UPDATE, or DELETE anomolies.
5. Relate the entities back together.
6. Make sure the tables do not allow bad data.

Example:

1. Identify the entities.
   These are the who, what, where, or when items. They are rarely directly related. Performing this operation first can solve many of the INSERT, UPDATE, or DELETE anomolies thay may be in the initial tables(s).
   e.g. Given a table client_interviews with the fields: ClientNo, ClientName, ClientPhoneNumber, InterviewDate, InterviewTime, StaffNo, StaffName, StaffWorkHomePhoneNumbers, and RoomNo:
   • ClientNo is data about a Client. This is a who.
   • ClientName is data about a Client. This is a who.
   • ClientPhoneNumber is data about a Client. This is a who.
   • InterviewDate is data about an Interview. This a what.
   • InterviewTime is data about an Interview. This a what.
   • StaffNo is data about Staff. This is a who.
   • StaffName is data about Staff. This is a who.
   • StaffWorkHomePhoneNumbers is data about Staff. This is a who.
   • RoomNo is data about a Room. This is a what.
   So I break it up into four entity tables:

   Table	Fields
   client	ClientNo, ClientName, ClientPhoneNumber
   staff	StaffNo, StaffName, StaffWorkHomePhoneNumbers
   interview	InterviewDate, InterviewTime
   room	RoomNo

   Don't worry about relating the entities back together yet.
2. Identify the primary keys for each entity table.
   Entity tables should have one and only one field as the primary key.
   If no single field can be identified as a primary key, one can be added.
   Note that for the interview table I need to add a primary key. If I only used InterviewDate then there could only be one InterviewDate entry of say '10/31/2024'. There could be more than one interview per day. The same is true of InterviewTime.
   I will denote that by putting an asterisk (*) in after of the primary key field(s) for each table:

   Table	Fields
   client	ClientNo*, ClientName, ClientPhoneNumber
   staff	StaffNo*, StaffName, StaffWorkHomePhoneNumbers
   interview	InterviewId*, InterviewDate, InterviewTime
   room	RoomNo*

3. Eliminate repeating groups of data. This is the 1NF rule. There can be no:
   • Lists of data in a field (e.g. The field Phone with data like 123-456-7890, 234-567-8901).
   • Repeating fields in a table (e.g. Fields named: Phone1, Phone2, Phone3).
   • Repeating tables (e.g. Tables named: HomePhone, MobilePhone).
   So the phone numbers in either case must be placed in a new table. There StaffWorkHomePhoneNumbers contains a list of data so this must be split up. This is done by modifying the staff table and adding a staffphone table.

   client	ClientNo*, ClientName, ClientPhoneNumber
   staff	StaffNo*, StaffName
   staffphone	StaffNo*@, PhoneNumber*, PhoneType
   interview	InterviewId*, InterviewDate, InterviewTime
   room	RoomNo*

   Note that the StaffNo field in the staffphone table is a foreign key relationship to the StaffNo field in the staff table. This is denoted by putting an at sign (@) after of the field. The primary key for the staffphone table is a composite key of the StaffNo and PhoneNumber fields. This ensures that any staff phone number can only be entered once for each staff person.
4. Remove the possibility of INSERT, UPDATE, or DELETE anomolies.
   INSERT and UPDATE anomolies can cause data in the database to lose integrity because the data entered is incorrect.
   DELETE anomolies can cause data to be lost completly to the database that should logically be kept.
   These types of anomolies are usually fixed in the first step because data is put in multple tables with the data in each table only related to the entity. That is the case here so the tables above need no modification.
5. Relate the entities back together.
   An interview consisted of a client meeting a staff person in a room at a specifid date and time.
   The linking table will have foreign keys to the other tables and I will denote that by putting an at sign (@) in front of the field name.
   I also need to identify the primary key and will denote that by putting an asterisk (*) in front of the primary key field(s) as before.
   So now I add a linking table meeting:

   Table	Fields
   client	ClientNo
   staff	StaffNo*, StaffName
   staffphone	StaffNo*@, PhoneNumber*, PhoneType
   interview	InterviewId*, InterviewDate, InterviewTime
   room	RoomNo
   meeting	ClientNo*@, InterviewId*@, StaffNo*@, RoomNo*@

6. Finally, make sure the tables do not allow bad data. Everything follows 1NF, 2NF, and 3NF and business rules.
   The way it is now, I could double book a client to multiple interviews at the same time, just with a different staff person or a different room. This should not be allowed. The same could be done for rooms or staff.
   So I need three more linking tables client_meeting, room_meeting, and staff_meeting with an added unique field and a changed meeting table.

   Table	Fields
   client	ClientNo*
   staff	StaffNo*
   interview	InterviewId*, InterviewDate*, InterviewTime*
   room	RoomNo*
   client_meeting	ClientNo*@, InterviewId*@, clientmeetingId^
   room_meeting	RoomNo*@, InterviewId*@, roommeetingId^
   staff_meeting	StaffNo*@, InterviewId*@, staffmeetingId^
   meeting	clientmeetingId*@, roommeetingId*@, staffmeetingId*@

   Note that the clientmeetingId, roommeetingId, and staffmeetingId fields are UNIQUE. I will denote that by putting an caret (^) after any unique field(s) in each table.
   Now, the client can only be booked for one date/time, and the same is true of the rooms and staff.