Normalisation Query: Which is correct?

[tim_tims33]

Hi, a little query for you specifically regarding 1NF. I know (at least I hope i do!) how to normalise, so please ignore giving me advice on 2-3NF. Also, this is not going to be implemented on any database software. It is a pure theory question we're looking at in school and I'd like to run the solution past you guys as I have my own query about the given solution (if that makes sense)



The attacments should show the the question and the given solutions.

My query is this. In 1NF, the solution could surely be

PATIENT-DRUG (PatientID, PatientName, DOB, Address, DrugID, DateOfPrescription, Dosage)

DRUG (DrugID, DrugName, Manuf.)

((* NOTE: i'm ignoring the DateOfPrescription as part of a composite key, because in reality, a PATIENT can receive the same drugs on a new Prescription))

I've always wondered in 1NF whether it matters whether you bring in foreign key from table above down to table below, or vice-versa!

Anyway, please clarify this 1NF situation if you can.

many thanks,
T

[orange]

In your first jpg, it's quite easy to see 3 "subjects/entities" --- Patient,Prescription,Drug

I you look at the definition here, http://support.microsoft.com/kb/283878,
specifically

First Normal Form

• Eliminate repeating groups in individual tables.
• Create a separate table for each set of related data.
• Identify each set of related data with a primary key.

Then I think you'd fail 1 NF because you still have A Drug relation.

But if you look at the definition/description of Normal Forms here, https://en.wikipedia.org/wiki/Database_normalization

The normal forms are applicable to individual tables; to say that an entire database is in normal form n is to say that all of its tables are in normal form n.

Newcomers to database design sometimes suppose that normalization proceeds in an iterative fashion, i.e. a 1NF design is first normalized to 2NF, then to 3NF, and so on. This is not an accurate description of how normalization typically works. A sensibly designed table is likely to be in 3NF on the first attempt; furthermore, if it is 3NF, it is overwhelmingly likely to have an HNF of 5NF. Achieving the "higher" normal forms (above 3NF) does not usually require an extra expenditure of effort on the part of the designer, because 3NF tables usually need no modification to meet the requirements of these higher normal forms.

and if you look at the definition and 1NF sample at http://www.phlonx.com/resources/nf3/index.php#intro

I think your sample for 1NF would fit.


I'm not really following your table above/below.

[tim_tims33]

I'm not really following your table above/below.

These are interesting articles. I recognise that beyond simple 'school' level stuff like this...higher normalisation can through in lots of complications.
BUT, keeping it at our 'lower' level, I will clarify on your query quoted above:

Here is one arrangement @ 1NF:

ENTITY 1 (Entity_1.Key Field, Entity1_Field, Entity1_Field, Entity1_Field etc...)

ENTITY 2 (Entity_2.Key Field, Entity_1.Key Field, Entity2_Field, Entity2_Field, Entity2_Field etc...)

As you can see, to relate the two entities, we need to invite the key field of Entity 1 into Entity 2...so there is a relation.

Alternatively, this could also work:


ENTITY 1 (Entity_1.Key Field, Entity_2.Key Field, Entity1_Field, Entity1_Field, Entity1_Field etc...)

ENTITY 2 (Entity_2.Key Field, Entity2_Field, Entity2_Field, Entity2_Field etc...)

Question: considering the example given in the original post...where i have two entities @ 1NF...does it matter which way I 'link' the tables to each other?

Thanks

T

[orange]

I think it's related to your context (that is what's most important to you, or in the business context what's important to the Business)

Suppose using this set up

ENTITY 1 (Entity_1.Key Field, Entity_2.Key Field, Entity1_Field, Entity1_Field, Entity1_Field etc...)

ENTITY 2 (Entity_2.Key Field, Entity2_Field, Entity2_Field, Entity2_Field etc...)

represented Entity1 represents Person and Entity2 represents Hobby

a few records


Entity2
1 Sailing Amateur
2 Reading Fiction
3 Reading NonFiction
4 Basketball Recreational

Entity1
1 Bob 1 23/9/1988 ---------Bob Sailing
1 Bob 4 23/9/1988 ---------Bob Basketball
2 Mary 1 2/12/1998--------Mary Sailing
2 Mary 3 2/12/1998--------Mary Reading NonFiction
2 Mary 2 2/12/1998--------Mary Reading Fiction

Reverse the entities
Entity1 represents Hobby, Entity2 represents Person

A few records

Entity 1 (if you have more fields of interest)
1 Sailing Amateur NonCompetitive SingleHull <20ft
2 Reading Fiction NYBestSellersList
3 Reading NonFiction ScienceAndTechnology
4 Basketball Recreational NonContact
5 Sailing Amateur NonCompetitive MultiHull
6 Sailing Professional Competitive SingleHull > 6 m
7 BasketBall OnTV AvidLakersFan

Entity2

1 Bob 1--Sailing Amateur NonCompetitive SingleHull <20ft is a hobby of Bob
1 Mary 2----Sailing Amateur NonCompetitive SingleHull <20ft is a hobby of Mary
5 Bob 1-----Sailing Amateur NonCompetitive MultiHull is a hobby of Bob
6 Bob 1-----Sailing Professional Competitive SingleHull > 6 m is a hobby of Bob
7 Bob 1 ----BasketBall OnTV AvidLakersFan - is a hobby of Bob

My feeling is that it really depends on the data you have and what's important to you. In the first set up, People seemed more import and hobby(s) was secondary. No detail really in the Hobby,
In the second set up, Hobby and the level of participation, more details of the Hobby than the Person.

There will be instances where it may be important. Those relationships where there is a definite relationship
Parent and Child(ren). Childeren could only have 2 biological Parents (let's not get to cloning). But Parents could have 0,1, or many Children.

The key to me, is you know you wouldn't leave the data structure in the proposed 1NF.

Did someone tell you you were wrong? I think you can find definitions that include or exclude what you have shown.

[tim_tims33]

Thanks for your very detailed answer.

That was my feeling too. It's more a matter of judgement as both alternatives work (from a purely technical point of view)

I guess, to some extent, it doesn't matter what a 1NF arrangment is....because who works with 1NF!

The only time it becomes an issue is when you're sitting an exam (...like me!) and the examiner wants to see your 1NF solution to a given table. (((I know they are very rigid in their mark schemes...and wonder if they would drop me marks depending which way round i did my 1NF.)))

Thanks for your insights orange. Food for thought.

t

[orange]

Happy to add a few comments.
Look at how 1NF example is set up here http://en.wikipedia.org/wiki/First_normal_form

and then this example http://databases.about.com/od/specif...normalform.htm

Take a look at this one, then try it with your data.