Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!!

share|improve this question

3 Answers 3

#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.)

share|improve this answer
    
thanks -- this is the direction I'm heading in with contacts. I didn't go into all that for this question since my question was more about whether the WallsMaster table needed to be separated into multiple tables. But since this is one of the first few real databases I've built it's nice to hear I'm off to a good start. –  maneesha Dec 21 '11 at 23:42

WallsMaster doesn't break any of the rules, if each of those fields is an independent property of the wall. I would suggest it's probably not worth redesigning...

This is assuming that the properties listed are the fixed set of properties. If there are going to be extra things added, then it would be worth splitting it out into a table which links properties and walls.

share|improve this answer

If it were me, I could see breaking-off data for Faces, Damage and Obstruction. That way the WallsMaster table only holds a faces_id, damage_id, and obstruction_id. Each of the special tables could look like this:

Table: Damage
Damage_ID int
Damage_Type_ID int

And then you'd have a Damage_Type table that looks like this:

Table: Damage_Type
Damage_Type_ID int
Damage_Desc varchar(20)

And its data would look like this:

Damage_Type_ID  Damage_Desc
0               water
1               grafitti
2               crumbling
3               erosion

You might consider this overkill. But this would allow you the freedom to add different damage types without having to alter your table structure(s). And it eliminates the need for "other".

share|improve this answer
    
So I get that this lets me enter new damage types into the Damage_Type table.. and likewise if I had an Obstruction_Type table, Faces table, etc.... but looking at the attributes in my WallMaster table, not all of them can be categorized like that. Would you suggest breaking ALL the attributes in to best-fitting categories? I realize while there are best practices to db design it's not necessarily an exact science so I'm just trying to gauge what others think... for this system as well as having a better understanding for future systems I develop. –  maneesha Dec 21 '11 at 23:38
    
I wouldn't say to do it to all attributes like that, but only in select cases where it makes sense. Sometimes too much normalization can hurt performance. So you need to weigh-out the maintenance implications of keeping it in the table, vs. how often you're going to have to JOIN to get it. –  BryceAtNetwork23 Dec 22 '11 at 0:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.