Well recall that 3NF is when you have
every field relating directly to the primary key. Valid 3NF also requires valid 2NF and hence also 1NF.
So let's check if it's 1NF and 2NF valid first. 1NF is straight forward, 2NF is where you must have non-key field relating to all the key fields in a table (to clarify, key fields are things like BookingID and ClientID for example).
For tblServices, I would include ServiceID as the primary key (and then use that in tblBookings too). Without it, you're probably failing 2NF. I'm going to assume this change has been made for the rest of my post.
As an initial remark, generally, to fix a 3NF problem, you shift that key into it's own table (similar to what you do with 2NF). With 3NF we go one step above 2NF, for example in tblBookings, it is now no longer allowable to have non-key fields that refer to ClientID (e.g. Client's Name) - they
must refer to the primary key - BookingID - alone. (you've done this with tblBookings
)
Another way to think about 3NF is that you've got a table full of data, where every record is 'named' by it's primary key. All the other fields will be items that describe this primary key. To use tblBookings as an example:
So we're looking at individual bookings that have been made. We refer to these by BookingID. Information that helps us describe BookingID is the reference to the Client who made the order, the reference to the Service ordered and the date which the booking was made.
Looking at tblServices: ServiceName and ServiceCost relates directly to 'ServiceID'. All good.
tblBookings: Looks fine.
tblPostcode: looks fine.
tblClients: FirstName, Surname definitely. Postcode, StreetAdress too. With password, I'm not too sure if it'd pass 3NF. It might be alright, but if you want to be pedantic, you could split that off that to something like tblUsers, e.g.
tblUserAuth:
Primary Key - UserAuthID
ClientID
Password
and then possibly any other data you'd need to log users in would go in here (e.g. last login date and all that jive, but that's irrelevant to this question/area of study).
There might be a better way of splitting that off, I don't like that UserAuthID thing, but I'm not too sure if we're able to to use same primary key be used twice (might want to double check this). Maybe it's an indication that splitting off into tblUserAuth is being a bit too pedantic.
Home
Help
Search
Login
