entity relation diagram

[hime]

i have this scenario for a school project:

You have been sub-contracted to design the database system for Marlowe Interiors. Marlowe Interiors is
a medium sized interior design company that does building work and decorating. An initial analysis of
Marlowe Interiors has identified the following requirements:
 Marlowe Interiors keeps a record of the jobs it performs. Jobs are for particular customers and
classified by job type (Single Room, Part-house, Whole-house). The full cost of a job depends on
the labour used and the parts used. Records of parts and labour should be kept separately.
 Labour costs depend on the type of work done. A job may have different sorts of work and employ
one or more types of worker (plumber, labourer, qualified builder, interior designer, electrician). A
worker will work on more than one job. The hourly rate of pay for each of these types of worker
will vary as shown below:

Type Rate per Hour
Plumber £50
Labourer £20
Qualified Builder £55
Interior Designer £39
Electrician £50

 A job may involve one or more parts. Examples of parts are: sink, bath, pipe, thermostat, wallpaper,
decking, door, light-fitting. The costs of these parts will vary and suitable test values should be
supplied as part of the initial implementation.
 Parts are supplied by various suppliers. The cost of a part could vary depending on the supplier.
 A customer record should be kept for the job with all the usual details such as name, address and
phone-number.

i want to make an erd.

i identified these entities :
job
job type
customer
labour
worker type
parts
supplier

the problem is that i am not sure if these are correct and also how can i beging structuring my erd.

any help appriciated

thanks

[griswolf]

You are missing join tables that associate:

• part with supplier (call that table supplier_part)
• job with supplier_part
• job with worker and billed time (Is this another table?)
• If workers can bill their hours at different rates depending on circumstance, then you also need a join table to associate worker with worker_type (otherwise each worker just has a worker_type column)

And I'm probably missing some other things too. I suggest you start by thinking about how you will query (what info will be needed?) and base your design on that. So your steps are:

1. Understand the specification
2. Think up all the queries that will be needed (payroll, billing, matching supplies to supplier bills... are there more?)
3. Design the queries the best you can, thinking hard about edge cases such as my idea that workers might fall into more than one type... and talk to your customer to resolve such issues
4. Create an ERD based on those queries (the queries and the ERD have a circular dependency of course. I find it best to start with the required output because that can make the ERD design a lot more concrete in my mind)
5. Repeat from step 1 until satisfied.

[hime]

my problem exact is where to start to draw the erd. are all the entities going to be drawn on the erd and am i missing something?

[griswolf]

You have to start. Daniweb isn't going to do the work for you, but will be helpful to make it work (better). Your problem isn't going to get solved here unless you show some effort.