Design

[SHETTY]

Dear Friends

We are in advertisement business -hiring own sign boards to our clients. our
boards are booked by clients on monthly basis at a perticular rate per
month. More than one boards are booked at once.

We have to verify wether particular board is occfied/vacant? when it will be
free for booking to next customer ? etc. All the boards booked will be
billed to customer as and required.

So I am looking for a model desing/template
Any idea please advise to me, If anyone has developed similar model please
help me,

Thanks

Ramesh Shetty

 

[Fred]

I noticed that nobody answered.

Your application is very simple in DB terms. But you didn't ask for help,
you asked for a template. Assuming that you have no other unstated data
recording requirements, you have a simple flat table with one column being
the expiration date of the the newest lease.
That field will be the answer to all of the questions that you describe.

Sincerely,

Fred

 

[SHETTY]

Dear Sir,
Sorry, I have not given you required information. I wanted to adopt some of
the fetures /forms from template, so i asked for template. I have already
designed data base and same is put to use. but I could not process the data
and extract reports required for me
We are in to outdoor advertisement business. We hire hoardings/sign boards
on monthly yearly basis for fixed amount. Once we hire the hoarding it will
be available with us for billing(letting out). We can let out hoardings till
the expiry of booking period with the lessor.

Hired hoardings will be billed to customers for a particular period(display
period). In display period hoarding period are occufied and not available
for further billing.
Once display period is over or when we process the data for each of the
hoarding we want to know wether that particular hoarding is occufied or not?
We have to look through the billing log sub table and find display ending
period from latest bill entry for each of the hoarding
after finding display ending period we have to update field Vancancy status
for each of the Hoardings in table ie wether vacant or ocufied.

Now I dont know how to obtain above result I am not good in coding or
querries
If you can give me the code which does above action for me I will be
thankful to you

I have already designed database & following is the table structure in my
Hoardingdb

1) TblBill :
BillID (PK)
CustomerID(FK)
BillNo
BillDate
BillDate_From
BillDate_To
BillAmount

2) TblBillLog:
LogID (PK)
BillID (FK)
HoardingID(FK)
Rate(per squire feet)
Amount
Display_Date_From
Display_Date_to

3) TblBusiness Units
BUID (PK)
BUName

4) TblBudget :
BudgetID (PK)
BudgetName
BudgetMonth(Date)

5) TblLocations
LocationID(PK)
LocationName
BusinessUnitName(FK)

6) TblHoardign List
HoardingID (PK)
HoardingName
Location(FK)
Lessor(FK)
Area(SquireFeet)
VacancyStatus

7)TblLessor
LessorID (PK)
LessorName
LessorAddress

8)TableBooking
BookingID (PK)
BookingDate
FromDate
ToDate

9)TableCustomers
CustID (PK)
Customername
CustomerAddress


Thanks & regards


Ramesh Shetty