Badges: 5
Report Thread starter 1 year ago
I'm not sure why, but for some reason, I have great difficulty in the normalisation process of databasing and I can't get past this section within my design part of my coursework. If anyone here has discord or smthn alike and is kind enough to help it would be much-appreciated:') I'm designing an accounting database and I need to separate workers constant static wage rates to the hours they work on a given day. If anyone is good at normalisation, hit me up on discord. Donk#4272
Badges: 19
Report 1 year ago
Structuring data straight into 3rd Normal Form is usually something that you can arrive at directly by relating it back to the real-world scenario you're trying to model, then applying some thought/logic to the data that way.

In your example, it sounds like there are a couple of different entities, with one entity being your worker, and the other entity being the shift they work.

e.g. -- Keep in mind that SQL only supports 3 different possible direct relationships (Constraints) between tables, which of the following makes most sense in real-world terms?
  • Many employees work one shift (Seems very odd to me, the employer is only going to be paying one person for that shift once, so multiple employees should probably not be allowed to work one person's shift, that doesn't really make sense to the real world, so it doesn't make sense in a database either)
  • One employee can work one shift (Also seems odd, unless no employee can ever last more than one shift in their job)
  • One employee can work many shifts (Makes sense - an employee might work hundreds of shifts in their job, so that also makes sense in the database)

You didn't really mention anything about the actual data, but from a real-world perspective, an employee would usually have some kind of Employee number or identifier (Maybe that would be their payroll number, or just an autonumber ID in a database, it doesn't really matter which - as long as there's a way to identify them). You mention that employees have a fixed wage rate (By which I mean each employee potentially has their own hourly rate, so that sounds to me like their hourly rate depends upon who they are, and nothing else -- or from a database perspective, the rate depends on their employee ID/payroll number). Obviously an hourly rate wouldn't be a key field either.

On the other hand, Each employee would usually work many shifts during their job; the hours for each shift depends upon who they are, but it also depends upon the day as well (I'd guess that a real payroll system would store datetime fields for the start and end of each shift, because SQL can easily calculate the number of hours between two datetime fields. That also solves problems like how to store overnight shifts, and what happens if someone works 2 shifts in the same day, etc).

However, using anything to do with the date/time as part of a primary key is probably a bad idea and difficult to work with, so for the sake of keeping things simple I'd expect there'd probably be an autonumber as a primary key for the shifts that they work too
(You usually can't go too far wrong if every table in your database uses an autonumber ID field as its primary key in any case - even if there is an obvious natural key to be found, it doesn't really hurt.)

Quick Reply

Attached files
Write a reply...
new posts
to top
My Feed

See more of what you like on
The Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.


What support do you need with your UCAS application?

I need help researching unis (25)
I need help researching courses (12)
I need help with filling out the application form (9)
I need help with my personal statement (77)
I need help with understanding how to make my application stand out (44)
I need help with something else (let us know in the thread!) (3)
I'm feeling confident about my application and don't need any help at the moment (15)

Watched Threads

View All