Villa Rental Database

[brow]

I have attached a copy of the database I am trying to build for a small villa rental company.

All I have done so far is the tables and relationships... Now though I am stuck. I want to be able to search availability and add other helpful features.

I am able to do straighforwrd forms but am confused about adding the functionality that I desire. Which is the main reason for wanting the database in the first place.

If anyone wants to give me a few pointers I would much appreciate the help.

Thanks in advance!

 

[Mile-O]

Firstly, you have a Total Cost field in tblAccounts. You don't need this as it's a calculable value done at runtime.

Next, your properties table contains a repeating group. The group is all the features of a property (i.e. Pool, Car Parking, Terrace, etc. ) I'm also curious as to why you have them as Text fields and not Yes/No (Boolean) fields. However, these should be listed in a new table with a further table included containing only a foreign key of the property table and a foreign key of the new features table. These two fields would become a composite primary key for this new table and will help to simulate the many-to-many relationship that is present but not factored in.

Once this is done, your search will become a cakewalk.

 

[brow]

Thanks for the quick reply!

Total cost: can you explain why it does not need to be in the table? I understand that it will be calculated in runtime, but does it not need to be stored for reports etc?

Property feature: where do I draw the line as to what is a property feature? Should the properties table just have...

PropertyID
ManAgentID
CategoryID
PropertyCode
PropertyName
Address
Location

...and the rest be put into tblPropFeatures? I will definetly make a few of the features Boolean (now i know the terms I might as well use them), others like town/beach refer to time/distance.

I think I have made the recommended changes - although I'm not 100% that this is what you mean!

Anyway, I have attached.

Thanks for your help thus far!

 

[Mile-O]

Total cost: can you explain why it does not need to be in the table? I understand that it will be calculated in runtime, but does it not need to be stored for reports etc?

Because you don't store calculated values in a database. To ensure robust data you do all calculations at runtime (the Third Normal Form of normalisation insists that you should eliminate all non-key dependencies*) in a form, a query, or on a report. For example, if you stored Price and Quantity then you save space by not storing Total as you can easily calculate Price multiplied by Quantity and if you stored the total but then somebody changed the price you would manually have to change the total - this runs the risk of working with data that is incorrect, especially if only the price has been changed and the person forgot to change the total.

As for the features, you didn't get what I meant so I've created a quick example.



* A non-key dependency is one field that relies on another field for its data when the field being relied upon is not the primary key.

 

[brow]

Thanks for explaining... I have spent far too much time working in excel, my mind had become clouded or is it shrounded!!?

The example helped. I am never too sure when it is best to do it like that or put items as fields in a table.

I have made the changes to mine... What next Monseigneur???

 

[Mile-O]

The next thing is to make queries of all your tables and sort the records as you want them when using them in forms - as recordsources, or in lists and combos.

When searching for property based on a selection of different criteria you can now use a multiselect listbox (just set its Multiselect property to Simple) and then create, with VBA, a query that returns results dynamically based upon the search criteria.

I've previously posted an example here.

 

[brow]

Ok this may take some time...

Thanks again for your help! I will post when I have managed to do it.

 

[Mile-O]

No probs.

I just looked at your relationships and you should, on them all, select Enforce Referential Integrity and Cascade Delete Related Records.

 

[brow]

I have done that for all but the connection between tblpeople and tblproperties as aparently the data in tblProperties violates referential integrity rules! Not too sure why?!

 

[Mile-O]

Have you done something to those tables since your original example? I just tried on that and it worked fine.

 

[brow]

Yeah, I was a little ahead of myself! Fixed it.

 

[Shama]

Hello, I am also creating a property sublet database and want to create a rental payment schedule to know the due/overdue dates along with outstanding amounts based on weekly, monthly, quarterly payment terms. Any help is highly appreciated.