| Regarding optional attributes and denormalisation [message #2809] |
Thu, 06 October 2011 06:01  |
rksk16it
Messages: 2
Registered: October 2011
|
Junior Member |
|
|
Hi Tony
Though I am not a user of radicore or PHP, I read your articles often, and try to apply them in my own projects. 
I was reading your article regarding normalisation and effective database design : http://www.tonymarston.net/php-mysql/database-design.html. And it was like 10th time I was reading it. 
In the section of denormalisation, there is a sub-section about optional attributes that exist as a group : http:// www.tonymarston.net/php-mysql/database-design.html#optional. attributes. There you said the following :
R (K, A, B, C, X, Y, Z) where:
1. Attribute K is the primary key.
2. Attributes (A B C) exist all the time.
3. Attributes (X Y Z) exist some of the time (but always as a group under the same circumstances).
4. Attributes (X Y Z) require special processing.
After denormalising the result is two separate relations, as follows:
* R1 (K, A, B, C)
* R2 (K, X, Y, Z) where K is also the foreign key to R1
What I can see is that you said that breaking of the original relation 'R' into 'R1' and 'R2' is denormalisation. But I fail to see any single normalisation rule which is broken.
1st NF : No repeating groups
2nd NF : No partial dependencies
3rd NF : No transitive dependencies
BC NF : Every determinant is key
4th NF : No multivalued dependency (if R doesnt break it, then probably R1 and R2 also dont break it)
5th NF : Cannot be decomposed into furthur smaller relations. (Again if R doesnt break it, R1 and R2 probably also dont)
6th NF (No use discussing)
Also, from the application point of view, R1 can be seen as a class 'customers', and R2 can be as 'customers_with_arrears' which inherits 'customers' as it follows one-to-one relationship with some extra attributes, effectively increasing the specialisation (thus inheritance).
Thus it looks like a better design overall, and I cant see any denormalisation. I would be grateful if you can help clarify it.
Thanks a lot
[Updated on: Thu, 06 October 2011 06:02] Report message to a moderator |
|
|
|
| Re: Regarding optional attributes and denormalisation [message #2812 is a reply to message #2809] |
Sun, 09 October 2011 05:39  |
AJM
Messages: 2368
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
It is breaking normalisation for the simple reason that all relations which share the same primary key are supposed to be combined into the same table. That is the starting point, and from there you begin applying the other normalisation rules.
Thus if an entity called CUSTOMER has 100 attributes you start by putting them all into a single CUSTOMER table. It is only when you step through the rules of normalisation that you identify reasons to split attributes off into other tables.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
