I hope to reach 3NF with my database that is not particularly well made but functions ok...My main concern is whether there is a need for perfect 3NF, if there is such a thing, and if I should make adjustments on any of the tables to get to a good enough 3NF?! help me just understand if the level of normalization form this db is 3NF and good enough to pass as 3NF...
*please bear with me, this is my first web project, therefore my first database!
Asked
Active
Viewed 767 times
1
Bfive
- 43
- 7
2 Answers
2
3NF usually, but not always, makes a lot of sense. At the very least, databases in 3NF tend to be smaller and the chances of inconsistencies reduces considerably.
Looking at your database, as a starting point, here are some thoughts:
- users. Make your primary key sensible. Is userid unique? Is the username-mobileno combo unique? Surely uniqueness cannot be defined by a combo of all three. A common strategy is to have userid as the primary key, with the username-mobileno as a unique constraint. If that works for you, see #2
- username-mobileno/userid. There appears to be significant confusion in the use of these three columns in the child tables. Only users' primary key should migrate to the child tables.
- servicepost child tables. servicepost_details and ratings. username appears to be redundant in these child tables (available in parent tables).
- bids. It appears to be another child of servicepost. username, mobileno appear to be redundant (available in parent tables).
- user_details. What's this table required for? If there is just one row per user, dump the table and move relevant columns into users.
- servicepost_details. Here again, is it possible to have multiple rows in this table for one in servicepost? If not, dump it, and move relevant columns into servicepost.
RudyF
- 805
- 1
- 10
- 16
-
thanks alot @RudyF...I get it now, but would i be able to register the user with the fields in the table while adding the further info from user details later on? – Bfive Jul 25 '15 at 22:53
-
userid is unique...and in regards to the username, can you help me differentiate between the user who posts the 'servicepost' and the one who bids for it? I thought I would do it with the username...should I use their ids?...and with bids, the date of the bid and the user who made it, as well as if it is accepted or not I thought needed their own table...was that wrong? – Bfive Jul 25 '15 at 22:57
-
"Would i be able to register the user with the fields in the table while adding the further info from user details later on?" ... sure, through an UPDATE statement. – RudyF Jul 26 '15 at 10:50
-
"can you help me differentiate between the user who posts the 'servicepost' and the one who bids for it?". In a true 3NF, you wouldn't have users.usertype ... because that is determined by information in servicepost and bids. Besides, can a "client" not become a "bidder" (like on eBay?). – RudyF Jul 26 '15 at 10:56
-
1"should I use their ids?" (in child tables). Yes, that is precisely the purpose of a primary key. Later, when you need "real life" information for say, reporting, use a join statement. – RudyF Jul 26 '15 at 11:02
-
thanks for the update...those are good points, I already have plenty of data in the users table and after log in is when they have an option to add further details or not...so would me getting rid of the user details table, and adding those fields to the users table mess the structure and flow up? and whats the best option to follow?...keep in mind my project is due tuesday, so major structural change may not be so advantageous considering the amount of work I still have remaining... – Bfive Jul 26 '15 at 12:23
-
what about that table? – Bfive Jul 26 '15 at 19:27
-
"What about that table?" Presumably, you are talking about "bids". Yes, I'd imagine that bids is required. Whoa! It looks like you've made changes to the database layout. This looks a lot better. What's the primary key for the table, bids? Is it servicepostid, bid? That works, on the (obvious) assumption that each servicepost will attract 0 to many bids. The tables users, servicepost and bids make sense to me, but I'm not sure how ratings fits in. – RudyF Jul 26 '15 at 23:09
-
About ratings: I understand that a servicepost is being rated, but is the userid (in ratings) a "bidder", the winning "bidder" or someone completely independent. If it's either of the first two, then ratings should be a child table of bids with a 0-1 relationship. – RudyF Jul 26 '15 at 23:10
0
Every normal form, from 1NF through 5NF was invented (or perhaps discovered) for a reason. There are consequences to departing from the normal form. As @RudyF has already pointed out, the normal form usually, but not always, makes a lot of sense.
If you are a beginner, the chances are you do well to ignore BCNF, 4NF, and 5NF for now. The consequences of departing from 1NF are that you lose keyed access to some of the data. The consequences of departing from either 2NF or 3NF is that the same fact is stored more than once in the database, raising the possibility that it will be stored inconsistently in two places, creating a contradiction inside the database.
As to your question of whether you have to adhere to "true 3NF" or not, this boils down to whether you are prepared in your database update applications to assure that a self contradicting database will never be committed, or whether a database that yields inconsistent results will bother you.
It's not hard to get to 3NF. You need a key for each table. Typically you name a key as primary key even if there is more than one possible key. A key can be composed of more than one column. You then make sure that non key data depends on the key, the whole key, and nothing but the key (so help me Codd). You can't answer the questions about dependency without reference to the subject matter.
When you are normalizing, don't be too concerned about speed or efficiency. You can tweak the design for speed before you build.
I hope this doesn't repeat stuff you already know. It's easy to learn this stuff. Becoming proficient is not so easy.
Walter Mitty
- 18,205
- 2
- 28
- 58