Hi
I am designing a database for a local garage which sales and repair cars, I have come up with attached ERD diagram could anyone give me any advice on it and let me know if there is something wrong with it.
Thanks for your help.
Kind Regards
HB 25
Hi
thank you very much for posting your ERD I like that kind of people who likes other opinions before they start implementing I think it is more reliable in work.
Lets see
I will not talk about the whole ERD i will talk only part of it when i get free time soon I will give you my full opinion if you like
first
customer -1 To Many- > Invoice -- I Agree
customer -1 To Many- > Booking -- I Agree
In car Table
the customer will have a car and some times the car will come more often to the garage
so the relation between booking and car would be one to many
Car -1 To Many-> Booking
and car with stock, what I understood the stock are the parts that will be installed in the car to be fixed
if it is you can't use this relation because it will cause duplication in the car row if the car had more then one part
and sometimes the part will be installed more then one time in the car (like spark plugs) so it is preferred to make it many to many
Car - Many To Many -> Stock
about Service/Repairer I didn't under stand what is that for
I have some other comments but as soon i get more time i will mention them
thank you very much
regards
Hi kronass
Thank for replying to my thread, I have managed to get my ERD done but I may need some advice on Normalization can you help with that?
If you can then I will post my normalization and you give me your advice on it.
Kind Regards
HB 25
hi
yes sure.
Kind regards
Some "things can go wrong" notes, especialy in economic crisis time.
1)A booking can be canceled and the can be booked again by another (or the same) customer at a later time.
So it is advised to modify the relationship between car and booking from 1-1 to 1-many and add a booking_cancelled flag. Only 1 record is allowed to have this flag set to 0, and the other records will be the booking history.
2)You should somehow join the invoice to the bookings. This can be 1-1 if there is a rule that one invoice will be raised for each booking, even if the customer (propably a very very very rich person) has many bookings at the same time, or 1-many if you can invoice many bookings at once. In this case you need another table to implement the relationship.
3)If the job card is related to the mechanic (seems logical to me) it is quite strange to have many mechanics doing only one job (remember the 1-1 relation requirement). Something is wrong there. Maybe there is another entity i.e Job Card Works related 1-n to Job Card.
Greetings
About the booking I think it needs modification
I put a simple one (In my opinion)
Where the customer can't book until he has a car (or motor Cycle or anything)
and in the booking we put booking status which they are
Pending --Waiting
Canceled -- Sometimes they just want to cancel
Delayed -- To other appointment
Finished -- Car is fixed :)
in case of delay we must know which booking was delayed and to which time
so either we create delay table that tells us the old booking and the replaced booking or we put a new column to the booking table that references to the old booking, both works
I drew the relation and put in image
This part was for the booking
Note that this relation ship will not allow you to make many cars in one booking (because it is simpler), so is it acceptable? or you want the option that the customer can have more then one car in the same booking
Why I put Customer twice in car and bookings?
because maybe the car belongs to a customer but other one do the booking (like the first time the father brought the car and he owns it the second time the son do the booking and the car is still belong o the father)
I will submit the Invoice the and the job cart as soon I get Time
Regards
Hi kronass
Thank you for your help, please find attached with this my ERD and Normalisation.
Kind regards
HB25
I just modified the ERD
if The ERD is Agreed we can make normalization
what do you think
Hi kronass
Yes I do agree with your ERD it does make sense this way. Now we need to move on to the next step the normalisation.
Kind Regards
HB25
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.