I had a quick course of database few years ago and I forgot a lot from there. So you can say I'm starting from zero. I will try to explain to you on what I want to do and a picture with what I did so far.

We have a small business at home to change tires on a car (winter tires, summer tires). When I customer comes and changes tires they usually left them in our shop and we store them on a shelfs in the basement. For easier explanation let's say we have only 5 shelfs labeled from A1 - A5.

The guy comes to change from winter to summer tires. A receptor takes his name, address, car model, tires specification (of winter tires). Summer tires comes on the car, and the winter tires goes into the basement and on the shelf (let's say they go on location A1). After they are placed on the location we update the database with the location of the tire. When he comes next year we do a SQL query to see where he has his winter tires and we change them.

After receptor makes a paper with his information (name, tires, etc.) she prints this list with QR code on it with his client it. The paper goes with tires to basement and when a worker puts the on a shelf, he scans QR code on the list and QR code of location (a1 shelf) and update the database with location information of where tires are being stored.

I hope you see what I'm trying to make.

Here is the link of the ERD I made so far:

ER Diagram

Can you please take a look and maybe suggest if anything needs to be changed.

Recommended Answers

All 21 Replies

If this is a "small business at home" then you are over-engineering this to a rdiculous extent. Who are the "receptor" and the "worker" - mom? dad? (not to mention anyone who actually changes tyres!) Just how big is your "home" that you need QR codes and an SQL database to keep track of (how many?) sets of tyres? And why ERD software engineering for such a simple application?

A simple rolodex-type card file is all you need.

It's not possible (is it?) that this is just a school assignment that you are trying to trick someone into getting involved with?

Well its not that small... we have around 50-100 sets of tires stored during the year.... we have 15 people empolyed... and its not a school assigment lol =). But ok if you dont want to help... you can just say you dont want to help and not attack me like that... I provided a picture what i did and ask you nicely...

Sorry if that seemed like an attack - it wasn't personal. I just didn't (still don't) understand why you would need so much Information Technology Engineering for something where a Rolodex would be perfectly OK. (and yes, I did look at the ERD... your strings seem generally too small, using integer fields for phone numbers is only OK if you are certain that every phone number will always be formatted exectly the same way, and speed ratings can be alphabetic. Do you really need separate fields for all the tyre's attributes separately - why not just "205/65R15 95H")

Apart from the IT justification and design, I'm curious about the business itself. Your figures imply that changing/storing 7 sets of tyres is enough to pay for an employee... unless there's more to the business than that.

Yes its also a mechanical shops... fixing car and doing car service... The tire thing is only 2 times a year when people change tires... And im thinking ahead if someday business will expand... this is why i have this er diagram. Why do you mean with strings seem generally to small? Are relations between the tables good ?

Relations look OK. 50 for an address or 20 for a complete car model seem small to me.

Phone number is NOT an integer
You don't add, subtract multiple or divide them
Phone numbers are strings. And often have spaces in them for ease of reading (integers don't have spaces in them, do they?)

Client address - should be split to include address and city, as well as being a larger field as mentioned above. Querying on city helps you work out where your clients come from and where to advertise.

Car model - should be make and model

Tires are not really a many to one for client, they are a many to one for cars. Remove that relationship. The client is linked to the tyre via the car, as the client can have one or more cars. and hence the client id is not needed in the tires table. This avoids you giving the wrong tires back (a truck just might have different sized tires to a compact car, even if it is the same client...)

If you want to do this properly, you need a date in and date out, related to the location and tire, in another entity - called storage perhaps. (A location can be used many times, and a tire can be stored many times in its life, and it can be stored in different locations each time)

Don't you want to record who handled the storage? Probably in the extra entity for in and out, as well as an employee table with a few details on employee, rather than just "Fred" stored it. And has the storage fee been paid? As a wild guess you are probably storing for varying times and charging based on time, so think about that as well.

Tires are not really a many to one for client, they are a many to one for cars <etc>

That's right (sorry I missed it).

I was also loking the realtion between location and tires. I have it set up like 1:1 but now i was thinking that maybe it could be 1: M ? one location can have more tires and tires can have one location ? Or is this stupid ? =). But thank you for your previous posts i will check and fix it and i will get back to you

It seems unlikely that a tyre can be in two locations a the same time! Do you group 4 tyres together, or deal with them separately?
Can a location hold more than one (set of) tyres? - That's a question about reality - the software should just refect the real-life answer.

yes i group them (4 together). One location(one shelf) can only store one set of tires at the time. SO the relation 1:1 has more sense right ?

Ok i made those changes that you recommend and i come out with this. Is it better?

http://imgur.com/a/jZz06

One location(one shelf) can only store one set of tires at the time. SO the relation 1:1 has more sense right ?
Right

I'm not convinced of the need for StoringProcess, unless you want to keep a history of what was stored when. If all you need is current state then a 1:1 Tyres/Location is enough
There are earlier suggestions about field types/sizes that could still be applied, also tyre types.

THIS year a tire can be in one location, next year it can be in a different location.

Therefore you have to have a way of knowing which location is used each year, even if a customer uses the same location several years in a row, because one year they might move away to another storage facility, and then return a year later, and clearly be unlikely to get the same slot.

Hence a location can have many tires, over time. Hence location to tires = one to many. It's also one to many because if you are storing tire details in full, and you are, the tires get swapped,

Your linking table will include the period it is stored for, so when searching for which location a customer used, you will also be including a year to find it.

So if i undertand you john_111 corectlly if i dont have another table between tires and location, then i have to link location to tires via relation one :M (one location has more tires)? I had this in the beggining but then i started to think. On one location i can only have one tires stored, this is why i put relation with location and tire to 1:1.

Because all i acutally need is the current state of the tires(where they are saved). Usually the customer that comes to change tires usess the same lcoation until he wants to store tires in our storage. Because he leaves winter tires at us and next year when he comes we put winter tires on and "usually" put summer tires to the location where the winter tires were....

So im thinking of leaving the relation between tires and location to 1:1. I just need a confirmation if this is the right think(if the whole diagram is set correctly so i wont have any problems later when i start creating the database)

One totally different question. Today i was reading how to set up the database and i come out with this. Need advice on hat you think about it. I was thinking of installing mariaDB and create the base in heidiSQL. I would export the sql file of the ER diagram from vortabelo. Then i would fill the data to database via heidisql gui... For writing a gui application i would use visual studio C# and connect to database through that.

Business rules - how thingsare done in reality - can sometimes overwrite ideas on how it should be done in a perfect database.

How to create actual system - make a baby simple two or three table database to practice your skills on before building the real thing. I personally would install a standalone web server such as usbwebserver (extract it's files, and it works!) and then you know the system will run, with php as the programming language and html to write the interface, but that's how I work. If a test db works, you know your real system should work.

Well i think i will not use php because this will not be a web application. I will make a from in C# in which worker can input data of the costumer. To do think that it is better to use a web service or can i just directly connect to database and when the pressbutton function is triggered in inserts the data to database (via sql statement)?

But thanks for your help so far... I run into some problems i will post it here and hope that you guys will help me again. So thanks for your help so far, have a nice summer and see ya =)

Best Regards,
Jure

With usbwebserver, you run it on your local computer or local network, you don't need to put it on the web at all. It's a standard way to create and test something you do intend to put on the web, but you don't need to put it online. But it's your choice.

Ok i have tried usbwebserver... I run it and everything looks good. Connected to phpmyadmin, created tables, fill in the test data. Then when i want to make a connection with datagridview in visual studio i have connection error. Its saying that access denied for user root @ localhost... Why is that ?

Hi Sibuns

Youhave moved on from the ER Diagram question to a discussion of usbwebserver. That's perfectly OK, but it would be better if you start a new topic, with a suitable title, to discuss that. There may be all kinds of people interested in usbwebserver who won't know this discussion is happening at the moment.

thanks
JC

Sibuns - - Speaking of grouping tires together 4-per-car - - do you ever do a vehicle that has more than 4 tires? Say a small dumptruck or panel van, that might have dual rear tires, or an even larger vehicle that might have more than one rear axle? I could see a given vehicle maybe having up to 10 tires before you even get into tractor-trailer setups. I've known ordinary citizens that drive a larger-than-typical-family panel van for their home businesses, and even if you're not doing those now, you did speak about future expansion...

We had a vihicles like that tbut usually they dont store the tires in our storage.... But good point.. maybe i will change the database a little. But im still having problem with connect with c# to the database with usbwebserver program running mysql service...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.