Thread: ER diagram help
04-03-11, 10:03 #1Registered User
- Join Date
- Mar 2011
ER diagram help
can someone check my diagram please?
Here is the specification:
Buttlins has 20 camp sites and each site has 100 pitches.
There are two types of pitch available for booking– those with Buttlins equipment (caravan, tent etc.) and vacant pitches where a customer will provide their own equipment.
A charge is made for the use of a pitch together with various charges associated with type of facilities required (caravan, tent, canopy). A families will rent either a pitch with Buttlins equipment ,a standard size caravan (sleeps 7-8) or a small tent (sleeps 3-4) or a large tent (sleeps up to 10), or will rent a vacant pitch and provide their own equipment.
About 25% of pitches are not available for booking but are let to private individuals who keep their own caravans parked there permanently. A caravan is allowed to stay on a site until it is 10 years old.
Private owners pay yearly ground rent for these sites at the beginning of each season.
All charges include the cost of water, electricity and gas.
A deposit of 30% of the cost will be paid in advance.
I have drawn something, but im stuck - can someone please check teh ERD diagram>
04-03-11, 13:40 #2Registered User
- Join Date
- Mar 2011
04-04-11, 16:01 #3Jaded Developer
- Join Date
- Nov 2004
- out on a limb
pitch PK doesn't feel right to me, I'd expect that to be a composite of SiteID & PitchNo
I'd expect a table which identifies the pitch type. think about it., if you were running the site, or if you were trying to book on line you'd wnat to limit your search to the type(s) of pitch that are of interest to you.
'avaibale for booking, wahgt does that mean.... presumably you could get away with that by creating a customer booking with a date say two years out. (you can alwasy chop back that date if the customers defaults or cancles the booking
Booking, Im suspicious of having a day and time for both arrival and departure, normally I'd expect a single datetime value for each
I suspect your status columns need to be FK's pointing to other tabels whciuh classify the status values
Address columns are pants, they don't comply with UK address types (which is 4, optionally 5 lines of address + postcode. in aprt that is why the post offcie is talking about dropping the "county" from addresses to bring that in line with other countries to 3 optionally 4 lines of address.
naimng conventions... column naems are clear and descriptive, which is great, however Im not a fan of second_nane and first_name, especially in the order they are given. may be better with forenames and surnames or givennames and familynames. bear in mind some custoemers may have more than one forenames and some may have more than one surname.
if you are hapopy limitign your customers to one address thats fine, likewise with phone or toehr forms of contact.
does it matter if people change address over the life time of their reealtionship with the company (thios would only be an issue if you needed to recreate an invoice after the fact.
I think the crows feet for the cusotmer booking realtionship is the wrong way round.
you may want to include soem other columns on the booking eg special terms & conditions, vehicle registrations and so on. you may also waht to know how many are booked on that pitch, there may be restrictions on soem sites which identify who may use that pitch (eg number of people, whether children and or pets are allowed and so on)
all in all a good start to your data modelI'd rather be riding on the Tiger 800 or the Norton
04-05-11, 06:28 #4vaguely human
- Join Date
- Jun 2007
The simple thing to do is go through each statement in your spec and see whether you cover that in your design. Just draw tables at first and lines to connect them. Check that each thing mentioned has a table and that any data mentioned can go into one of these tables. If it can't then you need a rethink. I wouldn't get too bogged down with fields at first because then you get too attached to the design and don't want to alter the table structure.
Things I thought were missing were:
- Buttlins equipment (caravan, tent etc.) and vacant pitches - these are types of thing you need to store info about ie cost.
- A charge is made for the use of a pitch together with various charges associated with type of facilities required - I didn't see anything showing what was on a pitch or anything to show what the charges were for each facility.
- A families will rent either a pitch with Buttlins equipment ,a standard size caravan (sleeps 7-8) or a small tent (sleeps 3-4) or a large tent (sleeps up to 10), or will rent a vacant pitch and provide their own equipment. - didn't see anything on any of this either.
- Private owners pay yearly ground rent for these sites at the beginning of each season. - didn't see this anywhere either. Perhaps best to just store a history of when and what they paid.
- A deposit of 30% of the cost will be paid in advance. - as above.