Database normalization -- does this example violate 1NF?

Question

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? That is, 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!!

Answer 1

I wouldn't split WallMaster into multiple tables, if these are the fields you need to model a wall then include them in the wall table.

• If a building can have multiple walls then I would factor the address etc into a separate Building table and link it to WallMaster in a one-many relationship (a building can have many walls but a wall can only belong to one building).
• You may want to consider factoring out Notes (in a one-many with WallsMaster) as this would give you the ability to enter multiple notes and keep a history rather than possibly overwriting existing notes.
• Consider a StaffID foreign key rather than StaffName on WallInteraction.
• Also I personally would rename 'WallMaster' to just 'Wall'.