2 Replies - 545 Views - Last Post: 18 February 2015 - 04:16 PM Rate Topic: -----

#1 streamin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 17-February 15

Design / best practices re normalization

Posted 17 February 2015 - 04:25 PM

I'm not a programmer nor overly technical but based on other postings I suspect someone here can provide appropriate direction. I have a web based application and am being told that I should change the design of one of my tables. It is a "settings" table that defines numerous aspects of the sites optional behaviors. Currently, the table design is as below. Some of the data elements are values, others are a boolean where 1= Yes and 0 = No. Every time we add a new setting we change the table design by adding a new column. At this point, the table has about 25 columns (25 different settings) but only 20 rows (20 different clients). It seems we add more columns (new settings) than we do rows of data (client entries)

ClientID------PageLogo------DisplaySignIn---------DisplayTimer-------PrintBarCode------TopButtonURL--------------------------etc.

AA....... ......aalogo.jpg.....................1............................1........................1..................../page1
1X..............1Xbiglogo.jpg...............1............................1.........................0...................../newpage
Z5.........-....aalogo.jpg...................0............................0.........................1....................../www.thispage

It has been suggested this "settings" table should look like below, This table design never changes, has only 4 columns, but hundreds of rows.

SettingID----------ClientID----------Setting--------------------Value

1............................AA...................ParkLogo....................aalogo.jpg
2............................AA...................DisplaySignIn....................1
3............................AA...................DisaplyTimer....................1
4............................AA...................PrintBarCode....................1
5............................AA...................TopButtonURL................/page1
6............................1X...................ParkLogo....................1Xbiglogo.jpg
7............................1X...................DisplaySignIn....................1
8............................1X...................DisplayTimer....................0
etc.

Please comment re best practice db design re normalization.
Thank-you

Is This A Good Question/Topic? 0
  • +

Replies To: Design / best practices re normalization

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14351
  • View blog
  • Posts: 57,547
  • Joined: 12-June 08

Re: Design / best practices re normalization

Posted 17 February 2015 - 05:55 PM

Certainly the second option makes a more robust relationship that does not need to add columns to it.
Was This Post Helpful? 0
  • +
  • -

#3 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: Design / best practices re normalization

Posted 18 February 2015 - 04:16 PM

While I like and prefer layout two, there is one thing to keep in mind with this.

In your 1st layout, you add a column and you know it exists. Therefore, you can just update the row for that client to set a value

with the 2nd layout, you can't just create a new setting and decide to update that setting, you would first need to check to see if that client has a row with that setting and UPDATE or INSERT accordingly. It's either that or you would loop through all your clients adding a row with a default value for your new setting ensuring that you can just use an UPDATE statement the next time the settings are saved. Again, still manageable and still a more normalized setup, just wanted to bring this up as something that you would run in to in switching over to this layout.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1