Design problems

[TracyBell]

Any chance you'd be willing to look at my DB to make sure I'm doing things correctly? I'm getting nervous about how everything is going to tie together in the forms, etc. So many tables! :-)

[jzwp11]

Sure I will take a look at it. You can post a copy with any sensitive data removed (or altered).

[TracyBell]

I'm sorry, I've started over so many times and this isn't working at all for me. I have attached a breakdown of what I think I need to have. Will you please take a look at it and see if I'm on the right track and then help clarify some of my questions noted. It's long so I put it into a Word doc.


My deadline to roll this out is Friday morning - yikes! My boss isn't willing to let me work on the "normalization" any longer because there are other enhancements they want to make that are on hold until this is done. Please help!


Again, thank you so much!

[jzwp11]

I have attached your document with my comments/recommendations. I don't have all of the answers since I don't have a complete understanding of your business process. But form what I see, this is a major database application and it would take even a professional a while to research, design, test and deploy a database like this, so I think your boss is being unreasonable in asking a novice to do this under such time pressures and not providing you with the necessary training.

My boss isn't willing to let me work on the "normalization" any longer...

Normalization is critical, ignoring such a critical part of a major application is well...stupid.

If my performance evaluation was based on this deploying this database under the conditions you describe, I would make sure my resume was up-to-date.

[TracyBell]

I agree that the deadline is a bit unreasonable, but I think he overestimates my database skills and thinks I should have been able to complete it by now. I’ve taken a semester-long Access class, but it can be hard to relate that content to the real world. I definitely consider myself more of a “power USER” than a “designer”. I have gotten a couple more days out of him, so hopefully today and tomorrow I can figure wrap it up and just tweek things later. Here are some questions I have from your notes previously attached.

tblContactInfo and tblRelationship: I’m not sure how to have a dropdown in a form without building it into the table. Isn’t referencing the primary key of another table going to create a dropdown field anyway? If not, as I suspect you’ll mention creating the relationship in the relationship screen rather than a lookup in the table, I had trouble with this before and could not create the relationship (kept getting errors. So I must have done something wrong.)

tblProperty and tblUnit: You mention moving the descriptive items to a separate table, but you need more info to be sure. Each of these items is a descriptor of a particular record. There will only be one of each of these fields per property or unit. To me it makes sense to keep them in the ‘parent’ table because of that, but if I’m missing something please let me know – I’m definitely not the expert. J

Utilities: Every property will always have each of those utilities. Building them into a separate table seems like it will add more work for staff to select the utility type and then type in the company, rather than just typing the company into a field already designated for that particular utility.

tblRole: I’m really struggling with the idea of putting properties and units in the same table. I just see too many red flags with that idea and I’d prefer to figure out a different way to incorporate the role. I appreciate you thinking outside of the box, I just see too many problems down the road with the right records showing in the forms and reports and too many blank fields in a combined table, as well as just keeping straight what is what.

Tenancy: these items change with every tenant and will need to be entered each time a new tenant moves in.

PMAgreement: this information is related to each owner’s property management agreement. Since an owner can have more than one PM agreement and, over time, a property could have more than one PM agreement if owners change or we stop managing a property then start again later.

I’ll get working on this and post a sample later today. If you have any more input in the meantime I’d appreciate it! Thank you SO MUCH for your help!

[jzwp11]

tblContactInfo and tblRelationship: I’m not sure how to have a dropdown in a form without building it into the table. Isn’t referencing the primary key of another table going to create a dropdown field anyway? If not, as I suspect you’ll mention creating the relationship in the relationship screen rather than a lookup in the table, I had trouble with this before and could not create the relationship (kept getting errors. So I must have done something wrong.)

You do not want lookups at the table level; they are best left to forms. I typically use the form wizard and have it create the form. Then in design view, I delete the control that is tied to the foreign key field. From there I use the combo box wizard to create the dropdown based on the table that holds the info I want. I then tie the combo box to the foreign key field (the wizard walks you through all of the steps necessary)

If you are getting errors in establishing the relationships it is most likely due to not having the correct datatype or not properly setting the primary key of the table. An autonumber datatype is equivalent to a long integer datatype, so the foreign key fields need to be of that type in order to join with the autonumber primary key field of the related table.

tblProperty and tblUnit: You mention moving the descriptive items to a separate table, but you need more info to be sure. Each of these items is a descriptor of a particular record. There will only be one of each of these fields per property or unit. To me it makes sense to keep them in the ‘parent’ table because of that, but if I’m missing something please let me know – I’m definitely not the expert.

What would happen if you need to add a new descriptive item? For example, let's say you start working with condos and a unit can have multiples (a two floor condo, a 3 floor condo). You would have to alter your table to accommodate this new descriptive item which would then require you to alter every related form, query or report that was tied to that table (I would not want to do that). If you treat a descriptive item as just another record, you would only have to add the new record. There would be no impact on your existing forms, reports or queries.

You would have a table to hold these descriptive items such as garage, heating system, A/C system and then tie them to the unit/property with another table. In that other table you can have fields that allow you to further define the type and the number of the item. You can also relate the various types back to the item to allow you to filter your selections.

tblItems
-pkItemID primary key, autonumber
-txtItemName

tblUnitItems
-pkUnitItemID primary key, autonumber
-fkUnitID foreign key to tblUnits
-fkItemID foreign key to tblItems
-fkItemTypeID foreign key that holds the various types of items
-NumberOfItems

tblItemTypes
-pkItemTypeID primary key, autonumber
-txtItemType
-fkItemID foreign key to tblItems

As some actual data, your item table may include the following
pkItemID|txtItemName
1|Heating System
2|Cooling System
3|Garage

The tblItemTypes may have records such as this

pkItemTypeID|txtItemType|fkItemID
1|Central|2
2|Window|2
3|Wall|2
4|Air|1
5|Baseboard|1
6|Hot Water|1
7|Gravity|1
8|Space Heater|1
9|Heat Pump|1
10|Steam|1
11|In-Floor|1

Utilities: Every property will always have each of those utilities. Building them into a separate table seems like it will add more work for staff to select the utility type and then type in the company, rather than just typing the company into a field already designated for that particular utility.

Again what happens if the unit has other related utilities: phone, cable, satellite. Do you want to alter your table 5 years from now and all related forms, queries, reports etc. ? Additionally, why type in the company name, have the user select it from a list (less chance for spelling errors, data consistency). You already have a table to hold company names.

tblRole: I’m really struggling with the idea of putting properties and units in the same table. I just see too many red flags with that idea and I’d prefer to figure out a different way to incorporate the role. I appreciate you thinking outside of the box, I just see too many problems down the road with the right records showing in the forms and reports and too many blank fields in a combined table, as well as just keeping straight what is what.

Don't call the table either property or unit, call it something else and then just add a field that tells you whether the thing is a property or a unit. Your call but I think you will have trouble relating people to the units and properties if they are in separate tables.

Tenancy: these items change with every tenant and will need to be entered each time a new tenant moves in.

Agreed. It would be on the many side of a one-to-many relationship.

PMAgreement: this information is related to each owner’s property management agreement. Since an owner can have more than one PM agreement and, over time, a property could have more than one PM agreement if owners change or we stop managing a property then start again later.

From what you just said, the PM agreement is related to both the property and the owner, so you will need a junction table and then related to that will be the agreements and then related to that will be the agreement details.