hi, am a newbie in database design and i really need some help. am supposed to create a database for the following scenario. "your organisation is a small firm that stocks equipment for hire to customers, you are required to help management create a database to simplify management of the business. Equipments are classified under varoius categories, the known ones been 1) Gardening equipment 2) Building equipment 3) Power tools 4) Access equipment 5)Decorating equipment 6) miscellanous. equipments are of different brands within each category. brands vary in their prices and are obtained from different suppliers, prices of equipments offered by the various suppliers as well as the delivery time for that equipment must be stored in the DB. customers are grouped into two entities; business customers- have a 24 hour support to replace any broken or faulty equipment that on hire, upon recieving a complaint an immediate delivery will be arranged to replace the faulty equipment provided it is in stock, otherwise, a refund will be administered. business customers are given varoius membership categories for discount purposes. these are silver(10%), Gold(25%), Diamond(40%). Private customers- do not have a 24hour support system, if in a similar situation, they must bring the faulty equipment in themselves and replace them with working ones or get a full refund if not in stock. Hiring equipment for private customers is restricted to those who live within 5 miles raduis from their address. the distance is measured on a map and the information is recorded." REquirements the computer is to be used when a customer visits the company to hire equipments, at the time the following processing is necessary for each transaction. -if not already known, the customers details are recorded -the equipment to be hired, with the return date recorded. - the equipment stock as recorded in the database is updated automatically. when business customers call for support to replace any broken or faulty equipmenton hire, a log must be made in the dB and whether replacement equipment is arranged and delivered or a full refund is given. When the equipment is returned, the following processing is required. -The actual return date entered -an invoice is printed which shows the item borrowed and calculates the total cost including VAT. the cost should be based on a rate for one day plus a rate for each additional day. the daily rate varies between weekdays and weekend days. -the stock figures are adjusted -business customers discounts will be calculated according to their membership category, while no discounts for private customers. quite long.....................but thats the scenario, i will post some of my ideas, that i hope you can help me with.
so far i have managed to create the following tables and thier entities! Customer Business customer Private customer Membership -customer id -business id -private id -membership id -customer full Names -customer id* -customer id* -membership type -Customer Address -membership id* -address distance -Discount applicable -Customer telephone no Category Brand Supplier Equipment -category id -brand id -Supplier id -equipment id -category name -brand name - Supplier name -category id* -Supplier address -brand id* -Supplier id* -Price
