Re: Regarding Database Design

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Mon, 11 May 2009 10:58:30 -0500
Message-ID: <JWXNl.32957$ZP4.10773_at_nlpi067.nbdc.sbc.com>



DeB wrote:
> Hi Faculties,
>
> Please accept my apologies if this query is out of context of the
> discussions in this forum.
>
> I've with me a proposed database design which is not normalized
> fully. There are some other tables that we plan to denormalize. The
> targeted database platform is Oracle Database 10g Release 2.
>

What idiot came up with that idea?

> For ex:- We have a table called user_groups which links the users
> and the groups table. But now we are thinking to eliminate this table
> and have a group_ids column storing comma separated values of groups
> ids from the groups table instead.
>

Then LEAVE IT ALONE... it is idiocy or even stupidity to do otherwise and you can quote me on that!!!

> The records will look like -
>
> groups
> ------
>
> group_id group_name
> 1 Admin
> 2 Operator
> 3 Power User
>
>
> users
> ------
>
> user_id user_name group_ids
> 1 User1 1,3
> 2 User2 2,3
>
>
> The targeted database will be a typical OLTP system. Please suggest
> if this approach to database design has any fundamental flaws.
>

Again, what idiot came up with that design as it breaks all rules for using the relational data model. In this model you will write 3GL code/scripts to parse your group privs which means a code-modification when you add/subtract group privs in the future - and never assume that "it will always be the same". Use the database as it was intended.

Rule #1: NORLMALIZE YOUR DATA (unless it is a datawarehouse, then there are just a few reasons to denormalize)!!!!

This is database basics. Learn them well and you will not screw up some poor scmuck in the future when you are gone. Never assume that because you wrote it that you will still have a job in 6 months, so make it easy to maintain on both you and your successor.

So, bottom line, it has too many flaws to list. If performance is an issue, then bring in someone that knows what they are doing to fix the the REAL problem and not make some idiotic attempts therefore making it worse.

>
> Thanks in advance

Your're welcome.

> DeB
Received on Mon May 11 2009 - 10:58:30 CDT

Original text of this message