J
JGT
Hi. I want to build a database for the administration of a habour for
small boats only (10' to 35'). The customers have to buy a spot in the
habour at one time but also pay an yearly fee. Lets call them owners.
Now, my program shall be able to send a bill every year to the owners.
However, the owner can rent out his spot for one year or two without
selling his spot. In that case the bill should go to the person that
rents the spot or dock. So what I wonder about is, what is best
practice trying to build a relational database for this purpose. Here
are the tables I started out with. (Sorry about my English).
tblBoatSpaces
NoOfBoatPlace (Unique No Like A001, A002..etc.) [Primary Key]
TypeOfSpotID (contains properties like width, price..etc)
MembersID
tblTypeAndPrice
TypeOfSpotID [Primary key]
Dock_Width (could be 2.0 mtrs, 2.5 mtrs, 3.0 mtrs etc.)
Price
tblMember
MemberID [Primary key]
FirstName
LastName
Address
AreaCode
City
StatusID [Foreign key]
tblStatus
StatusID [Primary key]
Status (registered as the owner or a person who do only rent for a
year; "a renter"?).
tblPayments
InvoiceNo (Auto increment) [Primary key]
InvoiceDate (Date/time)
NoOfBoatPlace (Text)
tblInvoiceDetails
InvoiceDetailID [Primary Key] (Auto Increment)
InvoiceNo [Foreign Key] (Number)
PayedAmount (for registration when the user pays)
DateOfPayment (meaning a column where the user of the program can
register
what date the bill was paid).
PROBLEM: When the owner has his own boat in his spot, there is no
problems. The Number of the Spot corresponds to him in the relational
database. However when he rent it out for a couple of years, the user
of the program has to register a new customer in the tblMember and then
change to this new membersID in the table tblBoatSpaces. The status of
the owner also have to be changed to NotActive or something else. How
can my program know what Spot (Space number) is his or who the dock
number belongs to? How can I do this and what do i miss? Is this
database OK according to Normal Form etc..? Best practice?
I do run my PC with Win XP and Access 2000.
Any comments or suggestions to this will be very much appriciated.
Thank you in advance.
Sincerely
Jan T.
small boats only (10' to 35'). The customers have to buy a spot in the
habour at one time but also pay an yearly fee. Lets call them owners.
Now, my program shall be able to send a bill every year to the owners.
However, the owner can rent out his spot for one year or two without
selling his spot. In that case the bill should go to the person that
rents the spot or dock. So what I wonder about is, what is best
practice trying to build a relational database for this purpose. Here
are the tables I started out with. (Sorry about my English).
tblBoatSpaces
NoOfBoatPlace (Unique No Like A001, A002..etc.) [Primary Key]
TypeOfSpotID (contains properties like width, price..etc)
MembersID
tblTypeAndPrice
TypeOfSpotID [Primary key]
Dock_Width (could be 2.0 mtrs, 2.5 mtrs, 3.0 mtrs etc.)
Price
tblMember
MemberID [Primary key]
FirstName
LastName
Address
AreaCode
City
StatusID [Foreign key]
tblStatus
StatusID [Primary key]
Status (registered as the owner or a person who do only rent for a
year; "a renter"?).
tblPayments
InvoiceNo (Auto increment) [Primary key]
InvoiceDate (Date/time)
NoOfBoatPlace (Text)
tblInvoiceDetails
InvoiceDetailID [Primary Key] (Auto Increment)
InvoiceNo [Foreign Key] (Number)
PayedAmount (for registration when the user pays)
DateOfPayment (meaning a column where the user of the program can
register
what date the bill was paid).
PROBLEM: When the owner has his own boat in his spot, there is no
problems. The Number of the Spot corresponds to him in the relational
database. However when he rent it out for a couple of years, the user
of the program has to register a new customer in the tblMember and then
change to this new membersID in the table tblBoatSpaces. The status of
the owner also have to be changed to NotActive or something else. How
can my program know what Spot (Space number) is his or who the dock
number belongs to? How can I do this and what do i miss? Is this
database OK according to Normal Form etc..? Best practice?
I do run my PC with Win XP and Access 2000.
Any comments or suggestions to this will be very much appriciated.
Thank you in advance.
Sincerely
Jan T.