3 Replies - 1358 Views - Last Post: 22 April 2017 - 07:58 AM Rate Topic: -----

#1 Watson98  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 18-April 17

1st Normal Form When Repeating Group doesn't relate to Primary Key

Posted 21 April 2017 - 08:44 PM

Say I have a database table that looks something like this when it is in unnormalised form:
Membership (Id, CustForename, CustSurname, AdviserId, AdviserForename, AdviserSurname, AdviserLevels, LevelId, LevelName, PurchaseId, PurchaseDate, PurchaseItem)


In this scenario, each membership has one customer, who has one adviser throughout their membership. Each adviser is only allowed to be an adviser to people who have particular levels (for example, like newbie or something), which is why "AdviserLevels" is present - it serves as a restriction. An adviser can be advising multiple customers. Each membership has one level at a time. Each membership has many purchases, but each purchase has one membership.

By third normal form it should look like this:

Membership ([b]Id[/b], Customer*, Adviser*, Level*)
Customer ([b]Id[/b], Forename, Surname)
Adviser ([b]Id[/b], Forename, Surname)
AdviserLevels ([b]Adviser*, Level*[/b])
Levels ([b]Id[/b], Name)
Purchase ([b]Id[/b], Membership*, Date, Item)


Now I know that first normal form means that there are no repeating groups, and all values are atomic. But I can't seem to be able to get into first normal form from the unnormalised form.

I can't seem to be able to deal with the "AdviserLevels" attribute because the Adviser details (like their forename) do not repeat, so really they shouldn't come out until third normal form is being done, right? If AdviserLevels did not exist in the table then it would be really easy to normalise, but I need to be able show the normalisation process of the database. This is what I've been able to do:

Membership (Id, CustForename, CustSurname, AdviserId, AdviserForename, AdviserSurname, LevelId, LevelName)
Purchase (Id, Membership*, PurchaseDate, PurchaseItem)
MembershipAdviserLevel(Membership, AdviserLevel)


But this can't be right surely? Its a problem because by third normal form we should have an AdviserLevels table like shown above. Is it even possible to normalise this that attribute in it?

This post has been edited by Atli: 22 April 2017 - 07:39 AM
Reason for edit:: Added [code] tags to make the formatted text clearer.


Is This A Good Question/Topic? 0
  • +

Replies To: 1st Normal Form When Repeating Group doesn't relate to Primary Key

#2 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4238
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: 1st Normal Form When Repeating Group doesn't relate to Primary Key

Posted 22 April 2017 - 07:38 AM

In the original table you have an AdviserId. Even though it's not a normalized table, I'd still regard this as an identifying value, specifying a certain adviser that is (perhaps) shared among many members.

So perhaps this version of your last structure makes more sense?
Membership (Id, CustForename, CustSurname, AdviserId, AdviserForename, AdviserSurname, LevelId, LevelName)
Purchase (Id, Membership*, PurchaseDate, PurchaseItem)
AdviserLevels(AdviserId, AdviserLevel)


This would solve the 1NF problem with the advise levels, while still leaving the Membership table violating both the 2NF and 3NF.

This post has been edited by Atli: 22 April 2017 - 07:40 AM

Was This Post Helpful? 1
  • +
  • -

#3 Watson98  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 18-April 17

Re: 1st Normal Form When Repeating Group doesn't relate to Primary Key

Posted 22 April 2017 - 07:44 AM

Thanks for the reply Atli!

This seems like the most viable option, but does the Purchase table not need to have a foreign key to "AdviserId", since MembershipId is not the primary key of the the first table that way?
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4238
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: 1st Normal Form When Repeating Group doesn't relate to Primary Key

Posted 22 April 2017 - 07:58 AM

I wouldn't think so, no. The purchases don't seem to have much to do with the advisers or the levels, so I don't see any need to link them to the levels directly.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1