Hi,

I'm a new member and also a beginner when it comes to relational db design. I'm currenly making a journal system as a simple exercise and I'm having trouble deciding where to place some of the information/fields. What I would like someone to do is to check out my design (attached jpg) and give me some input on the practicality of my design. Should I move any of the columns from one table to another (E.g. should I delete the table T_Contact and instead put the fields email and phone in the table T_Patient?) How far is it meaningful to normalize the db?

I'd be grateful for any input.

/Kris

Why are all the non-key columns nullable?

Why do you have phone numbers without names? There's apparently no way to associate a phone number with a patient.

Aim to achieve Fifth Normal Form unless you have good reasons to do otherwise. Only you know your data though, we can't design it for you in a forum.

Pure forgetfullness, I had the schema on paper and dl the first design tool I could find to be able to post a picture in here. I guess I was a bit sloppy.

Why not? If I relate T_Patient with T_Contact then there's an obvious association, isn't there?

Thanks. And yes I know, I'm not asking you to do my design for me. The question of how far normalization is practical seems to be contended. I've seen several proponents arguing that 3NF is enough for real world applications and I was curious to see what the members of this forum thought about this.

What's with the naming convention T_?If you're going to prefix all your table names with that, you might as well be consitant and rename your columns in the same fashion:
T_User ( C_UserID, C_Name, C_Role )

Seriously, this notation is redundant and pants

When the accelerator got jammed on in my dads Morris Minor he coasted to a stop, turned of the engine and popped both the boot and the bonnet.

From the bonnet he produced one of his trainers and proceeded to take the lace out. He then replaced the broken accelerator return spring with the aforementioned shoelace.

It was enough to get us to the nearest service station, but that's not the point is it

Well I'd like to use address (instead of addressLine) as a column in the table Address and thought it would be prudent to avoid confusing queries by having to write statements such as select address from address... and it's easier to use a prefix for the table once instead of adding prefixes to every column.

That's another point: is varchar(40) enough for address?

Tables rarely have a single record in them, meaning they are collections... So I tend to make their names plurals of what they contain.

So that table would become "addresses", just as I'd rename your T_User to be called "users". This also removes the apparent confusion from your query.
Makes sense to me - read the query aloud if you don't believe me

Well, I live in Sweden and I find it hard to believe that there's an address exceeding the length of 40 characters here, but I can see how it would be a issue for someone living in the UK (Welsh local names can be pretty long from what I've heard.)

Oh, I believe you alright. The prefix T for table is what I use after watching Learnkey's Database specialist design course. If using this convention results in people feeling discomfort/annoyance I will of course change it.

Just referring to your answer on another thread - 3NF is the prevailing opinion here. I am leaning more and more towards 5NF as time goes on.

pootle flump ur codings are working excelent.

True enough, I was too hasty in using my Morris Minor story. 3NF is acceptable, but why not strive for something higher

Mr Achivist, do you have a link to that naming ocnventions discussion from a few months back?

It's a highly opinionated subject, and there is no single correct answer. The blanket statement is "consistency is key", but it's very loose in it's definition.

I'm simply offering up my 2cents, doesn't mean you have to take it

It's a good story and I'm busy as blazes reading up on 4NF and 5NF. Of course, statements like "There must be no non-trivial join dependencies that do not follow from the key constraints. A 4NF table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys." doesn't exactly help with the understanding

Is “beyond 3NF” unnecessary? It might be, but probably in many commercial situations it is
unnecessary. Remember that application SDKs are just as powerful as database engine structural and
functional capabilities. Extreme implementation of normalization using layers beyond 3NF tends to
place too much functionality into the database. Why not use the best of both worlds—both database
and application capabilities? Use the database to store data and allow applications to manipulate and
verify data to a certain extent. -- Powell, Beginning Database Design

No - -but there's examples all over the place:
http://www.dbforums.com/archive/inde...t-1616014.html
http://r937.com/nounparis-prepin.html

pootle flump ur codings are working excelent.

I'd agree taken to its extremities it is a wee bit silly...... however I do think there is a role for identifying what are queries and what are tables, unless you deliberately want to obfuscate whether its a query or a table.

The Hungarian notation was I suspect more of a This is the way real programmers using C do it (because of the well documented need for such a convention in C because of its inherent design) and therefore toy programmers in Basic MUST go the same route.

it would be silly to subtype the column names
eg
intID integer autonumber
strName string

..especially as with a good column naming convention you can (shoudl) be able to deduce the datatype form its descriptive name.

..it also helps when on a bughunt based on user error report to hear them cite its frmMain, or rptWhatADoozy.. but thats within the confines of Access.. I don't pull such stunts in other environments.

In a legacy framework we had a table called tblUsers - it stored current users information and there were several child tables storing the users' permissions etc.

When a user account was no longer needed it was removed from tblUsers and added to tblExUsers. Now, what happens when a user account was then needed to be re-enabled (e.g. someone came back to the company, or the account was mistakelnly deleted) - that's right, all permissions and settings were lost because of the previous delete.

Now, because of the way the legacy framework was designed, we couldn't have disabled accounts knocking around in tblUsers, so we had to improvise.

The solution was to change tblUsers and add a "is_deleted" boolean field, and rename tblUsers to something else; in this case "users".

So that the legacy framework knew no better, a view was created of this table of all columns excluding the is_deleted oclumn, with a where clause leaving only active accounts... and the view was called tblUsers.

obfuscation is the wrong word

i think a better phrasing would be "unless you deliberately want to take advantage of sql's orthogonality, which of course was why sql was designed the way it was, on purpose"

no more or no less silly than typing the tables