Ms-access – Does this database table violate normalization rules

ms accessnormalization

I’m working with Access 2010.

I’m developing a database for a public art program – we paint large scale murals on walls.
The database tracks blank walls in the city as potential sites for murals. It includes information about the building itself and the surrounding property such as the lot the wall faces.

My main question is about the WallsMaster table. As you can see there are about 30 fields… and about 10-15 more that I’m thinking of adding resulting in 45 fields, maybe even more.

As far as database performance is concerned:

  • is it better to separate these out into multiple tables or keep them all in one?
  • Should I break up WallsMaster and have another table that cover my general categories within this table…. maybe called Damage and one called Obstruction and one called FacesLot etc… and then set up FK relationships between them and WallsMaster?

I am thinking about normalization rules… just not sure if these qualify as groups of related/repeating data as far as 1NF is concerned. My understanding is that is more about not having a table with something like AuthorName, Book1, Book2, Book3, etc.

Here’s a rough schema of my database:

Table: WallsMaster
WallID (PK)
StreetAddress
City
State
Zip
BldgName
Occupied_or_Vacant
Faces_Direction (NSEW)
Residential_or_Commercial
Historical_Property (Yes/No)
Visible_to_traffic (Yes/No)
Faces_Parking (Yes/No)
Faces_Fenced_Lot (Yes/No)
Faces_Abandoned_Lot (Yes/No)
Faces_Garden (Yes/No)
Faces_ParkPlayground (Yes/No)
Faces_street (Yes/No)
Wall_Surface (Lookup: Brick, Stucco, etc)
Damage_Water (Yes/No)
Damage_Crumbling (Yes/No)
Damage_Graffiti (Yes/No)
Damage_Other (Yes/No)
Obstruction_Trees (Yes/No)
Obstruction_Powerlines (Yes/No)
Obstruction_Other (Yes/No)
Number_Stories
Height
Width
Image (Link)
GoogleStreetView (Link)
Notes (memo field)

Table: WallContacts
ContactID (PK)
WallID (FK)  many:many.  i.e., one wall can have many contacts (owner, manager, tenant) and one contact can be affiliated with many properties (i.e., one owner owns several walls)
FirstName
LastName
Address
City
State
Zip
ContactType (Lookup: Owner, Manager, Neighbor, etc.)
Phone
Email

Table: WallInteraction  (Catalogs each time our staff talks to someone affiliated with that wall or conducts an inspection of the property, etc)
InteractionID(PK)
WallID (FK)
StaffName
Date
InteractionStatus
Notes
(this may expand to include more fields as we work with this more)

Thank you!!

Best Answer

#1. It took me a minute or two to find the problem. In it's current form, your WallContacts table requires one record per person per wall. So, if Joe is the owner of a building with 2 walls, he will need 2 records in WallContacts (one for each wall). This is because you're storing the WallID in that table.

Try this: Remove the WallID from WallContacts. Create a new table:

Table: Walls_vs_Contacts (I can't think of a better name)
ContactID (FK)
WallID (FK)

This table will serve as the go between between WallContacts and WallsMaster. So when creating queries, you join WallsMaster to Walls_vs_Contacts to WallContacts.

#2. A less important issue is that your WallInteraction doesn't include ContactID. Also, WallInteraction table can't properly record interactions between the staffmember and 2+ people. If this is an issue worth fixing (that's up to you to decide), you'd have to make an additional many-to-many table like in #1:

Table: Walls_vs_Contacts (I can't think of a better name)
ContactID (FK)
InteractionID (FK)

#3. Depending on how many staff members you have, you might want to make a table with a StaffID and StaffName fields. Otherwise, WallInteractions.Staffname will fill up with "Sheryl","Sherri","Sherryl LastName","S. Lastname", etc. making it impossible to search for all interactions involving her.

Otherwise, you're off to a solid start. I like how you identified and properly named the unique keys in advance. (Oh, and if you think I'm wrong, I probably am.)

Related Question