Best structure for front end? Recommendations needed.

[kacey8]

Afternoon guys,

Hoping for a little advice on a front end structure, for clarity I will explain the system in full.

I have a proposed database with the following structure

tblLandlord - Contains data for the landlords property
tblRenewal - Contains data for a specific renewal, each property can have multiple renewals, linked to Landlord with foreign key to LandlordID
tblFee - Contains the various fees associated with a renewal, each renewal has multiple records (1 record per each fee)

^ These tables are the ones that contain the customers data.

Schema is here

Open to feedback on my layout.

tblFeetype - Simple table contains the type of fee ie list source)
tblRenPeriod - Simple table contains the period for renewal ie list source)
tblTenType - Simple table contains the tenancy types
tblBranch - simple table contains the branches

^ These are tables in the DB which power certain "list" or "options" for certain fields, probably irrelevant for the discussion but including just incase.


So this works in that a Landlord may have Property1 on the system, property one may be linked to Renewal1 and Renewal10, Renewal1 will have Fees1,2,3,4 associated and Renewal10 has Fees10,11,12,13,14 associated.

My plan for the Forms is as follows and would love input.

Rough diagram of proposed front end. The stages are for each instance.

Completely new entry
1 - Search database (I want them to search all the time to stop them just adding new entries)
2 - No entry, box asks if they want to add a new entry, Yes = sends to new landlord - This form collects information stored in tbllandlord
3 - Button on page once record saved asking if they want to add a renewal
4 - Opens renewal form for data stored in the tblrenewal
5 - Continuous subform on page allowing fees to be added to the renewal

Existing record, new renewal
1 - Search database
2 - Landlord found, goes to record immediately no message
3 - Option to view existing renewals
4 - Option to add new renewal
5 - Renewals form same as Completely new entry


I hope this makes sense to you all.

I have some questions about how I'd get the forms to work, ie "add new landlord record, then click renewal, how does the new form it's adding a renewal for the landlord just added?

 

[Uncle Gizmo]

I'm puzzled because from what I see I assume you only have one landlord and one property. I don't see how you can have a landlord with more than one property?

 

[plog]

First, forget about input forms. Those are the last things to worry about. Tables then Reports/Queries then input forms. Second, I agree with Gizmo about landlords owning multiple properties. Also, where does the initial agreement data go? Seems weird you aren't storing that information in a database, just renewals.

Third, tables with only 1 real field (autonumbers don't count) don't really need to exist. Just store the actual value in the external table that links to it.

Now here's table specific ideas:

tblLandlord - what's AspasiaID for? Not used as foreign key anywhere.

tblRenewal - RentIncrease is unnecessary--if you know the CurrentRent and the NewRent, its simple math--don't store calculations.

tblBranch - what's a branch?

 

[kacey8]

Sorry,

Landlord/Property are the same thing (the tbllandlord stores information about a single property and is unique to that property) tbh could be reworded to tblproperty

AspasiaID is an internal ID from our inhouse system where some data comes from. (we need this to reference back)

RentIncrease is not a value box, this is a Yes/No (we have to store confirmation the Landlord has asked for a rent increase (the rentincrease amount is a calculation like you said and not stored)

tblbranch - stores a list of the branches for "RefBranch" so if a referal takes place they have to choose the branch to refer too, "tblbranch" contains the branches they can choose

 

[kacey8]

To explain where I was going with the following tables

tblFeeType
tblTenType
tblRenPeriod
tblBranch

These tables contain items that need to be amended by users (add new Fee types, new TenTypes, RenPeriod, Branch etc.

So for example, a user adds a new Fee Type "TDS Fee"

When the user is then adding a new fee to the renewal, the Feetype field in tblfee will have a drop down and powered by the "tblFeeType"

Same with branch, they need to add a new branch, they add to "tblbranch" and when using the field "RefBranch" the new branch is in the list.