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)