Hey!

hey there

sorry it took me more than what I said

It's ok. I'm the one asking for help :)

Can you make any sense out of my revision?

It does make sense. Question is whether it implements your idea accurately.

I'm talking about Malfunction_report specifically

Ok

what is it in this third revision? is it a collection of reports or is it a single report?

I mean a report about a single malfcuntion vs multiple malfunctions

Well, I could remove all foreign keys but the car and use the queries to get the informations I need

yes, there seems to be some redundancy at this point, but it may be necessary depending on how you are imagining the process

if the intention is to record the reports prior to creating the corresponding appointment, then you have to record both car and customer

my concern is that if you do record those and later you link the report with an appointment, which also has car id and customer id, then it is a redundancy but more importantly there's a possibility of discrepancy

Well, I could just use the date of the appointment and query to have the db return all the malfunction reports since the last appointment

I mean, nothing prevents you from linking to an appointment that has nothing to do with this report, unless I'm missing something

I forgot to ad a customer to the appointment. It's important to trach which customers had what apointments

true

that's probably a 4NF issue

unless we can figure out how to avoid the redundancy

The shop wants to keep track of the malfunctions which occurred since the last time a mechanic worked on a car, sicne they want to track the quality of their work

@AndriyM what redundancy do we have now?

car id and customer id both in appointment and malfunction_report

Well, carID is a requirement. Now it has become the only thing that ties them together

you mean you may decide to get rid of appointmentID in malfunction_report?

ah, you don't have it now...

Yeah

Now the If you want to, could you look at the sheet called "original"?

ok, looking at it

:)

The first sheet is being reduced to be more minimalistic to comply solely with the business requirements and no common sense

ok, a customer comes and complains about a malfunction or malfunctions

those are recorded in malfunction_report

suppose a regular checkup is not due so it's just about the malfunction(s)

an appointment is created after the report

and performed

Yep :)

And until it's been performed, it's called and displayed as a "booking" in the view

Do you think the rest follows the 3NF?

I think it is. The absence of direct connection between reports and appointments is kind of bugging me a little, but I can see how it could be derived. That doesn't seem to be a 3NF issue anyway.

Great :)

Well, all the statistics that will be derived from this don't really show either :P

Then I think that I can finally start populating my database!

Thank you for your help!

My pleasure!

Now, I just have to withstand the bashing of my other adviser; he's not happy at all with that I want to make logical assumptions like that there's more than one address, that employees can also be customers and not linking the two would be duplication of data if you want to store the employee's home data

on the other hand, a customer doesn't have to reveal their home address, they could specify a working address

Why make things you're not paid to do

true

Well, the customers have many options in the schema I sent you; see the "address_type" table?

Home, billing, mailing...

That table classifies it for the system :)

but in this schema they will still file only one address

Though in this country, it's rare to have more than one address

oh yeah

I thought it would form a cluster in the CustomerAddress

There can be many customer id and address references and types, but only one of each combination...

if you use a linking table, you may want to find a way to make sure there's at least one address

I think there was a question on SO about how to enforce something like that

CustomerAddress is not good enough?

it is, it makes perfect sense when you want to have several addresses per customer

it's just that it may contain no address for some customer(s)

and the shop wouldn't want that, would it?

Well, you CAN program the app to handle that

but I see the flaw in the design

But that could also be a feature

Not everyone has an email

Not everyone has a phone

Although that's becoming exceedingly rare

As long as you can pay on the spot, the business technically doesn't care

But you kinda want some kind of way to contact them so you can send your awesome new offers

yeah, a concern but maybe not a primary one

nods I doubt it's something that'll make me fail though

But it's worth noting since I'm not just "passing the assignments" I want to learn this stuff and learn to do it well

so in the end you are not using a linking table for appointments and malfunction reports

it makes sense in this revision, but do you think you will need to defend it?

No, I really don't think I will

As I mentioned, my teachers are really incompetent. I could make a launch ready application and they could fail me because they've misunderstood something vital, or worse; fail to notice vital flaws and pass me with a high grade

The later is worse because I'll probably never know because they only tell you if you failed or passed

In my student years I quickly got used to the fact that my teachers were unlikely to help me much in my learning. Those were useful years nevertheless, they just didn't add much to my professional knowledge

(I'm talking about university here, not secondary school.)

Yeah, that's why I've been teaching myself this course.

The education isn't generally this bad, but the school bought the service of teachers from an IT company. And they sent real copy-paste monkeys to try and teach us. It was a terrible failure and it only ate all our time and taught us nothing

Looking at someone googling why their code isn't working isn't especially productive :/

Oh dear :)

On the other hand, we had courses where we almost never saw the teacher :)

It probably had something to do with the fact that we had classes in the afternoon/evening hours.

And one other reason that we didn't witness a teacher googling anything could be that there was no google at the time :)

I'm attending a career school so most studying is done on your own, but you're supposed to have lectures two days per week to guide you. Epic failure here. The first teacher I had though was absolutely wonderful!

sorry to bring this up again but I remembered something I meant to tell you in connection with the child table I suggested earlier

I'm not sure if this should be a concern with the database side, I just wanted to mention it

the main idea about it was to help in composing an invoice

so they would essentially become invoice items

Ah

the fact that they would also help in linking the appointment with individual complaints (malfunction reports) was more like a bonus

I'd love to learn more about that when I'm done with this project. The sad part is that it has to be done by wednesday so I have to be selective in the parts I focus on; which more or less is only what I need to know to build this app :/

I hate that situation :(

learning constraints :/

right, this sucks indeed, but hey, this is not your fault and what you've done as your first ever database design is really good and consistent

Thank you! I needed that.

the stress of having so little time really drains the self confidence

Zeitnot. I hate it.

Yeah :/

I love populating my database though

it's monotonious work but it's teaching me to use postgres ^^