Table Setup and Relationships

[CoachBarker]

I am putting together an application for a Self Storage business for a friend of mine and could use some advice. It has been a few years since I have built an application using accesss, I am a .net programmer and the db is usually always built for me. The business has 107 storage units. The application is built around the storage units not the customer. So I need to track based on the storage unit number: rent payments, payment dates, comments from the customer or owner(can be one (unit) to many (customer/owner)eg: paying late, problems, Questions, etc), start date and end date of rental. Other than a customers table, what tables would I need for the storage units and their relationships?



Thanks
Coach Barker

[NoellaG]

Hi,

can't be sure without a thourough analysis, but it looks like you'll certainly need:

tblCompanies: to store the customers and eventually providers
tblContacts: if one company can have many contacts
tblUnits: data about the storage units
tblContracts: which company rents which storage units from date till...
tblContractsComments: per contract the comments, questions, ...
tblTarifs: if used, rental fees per unit or m3 or whatever; valid from .... valid till
tblContractPayments


there will be probably some more tables depending on your analysis...

succes
NG

[CoachBarker]

Something along these lines?

tblCustomers
CustomerID
FirstName
MiddleInitial
LastName
Address
City
State
ZipCode
HomePhoneNumber
CellPhoneNumber
WorkPhoneNumber
AltPhoneNumber
Email
Active

tblStorageUnits
StorageShedID
StorageUnitNumber
BuildingNumber
StorageUnitSize
Available

tblRentals
RentalID
StorageShedID
CustomerID
BeginningDate
EndingDate
PaymentDate
PaymentAmount
Comments

I realize all contact info for customer should be in a separate table, but I will worry about that later.

[NoellaG]

Looks like a good start, but be carefull, if more then 1 payment is due for a rental (for instance 1 payment each month, they should be in a separate table

greetings
NG

[CoachBarker]

why? If each payment is recorded as a new record with RentalID as the primary key, then shouldn't I be able to have multiple entries for each unit?

[NoellaG]

Let's say the customer rents a storage from January till August and pays each month. For each payment you'll be wanting to enter the PaymentDate and PaymentAmount, but you don't want to repeat each time:

StorageShedID
CustomerID
BeginningDate
EndingDate
Comments