Redesigning a database from scratch, where to start...

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Design Ideas and Questions » Redesigning a database from scratch, where to...

Redesigning a database from scratch, where to start...

Rate Topic

Display Mode

Topic Options

Author

Message

jasona.work

Posted Thursday, October 25, 2012 11:21 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:26 AM
Points: 467, Visits: 2,345

So I'm trying to help out our developers, and trying to make the DB for the application a little more "sane" and normalized. The devs are doing most all access through a .NET application and I'm not worrying at the moment about what changes would need to be made to the application.

I know I'm not going to get everything right, and that's OK too, this is a start point for cleaning up the existing DB before this application goes out to dozens / hundreds of clients and fixing / cleaning up the DB becomes a logistical nightmare...

What I'm looking for, is a suggestion for a starting point. I've got an empty copy of the DB I can use to make sure I keep the same column names, and I expect to be creating new tables.

So, my question is pretty much, where to start? If this were you doing this, would you start by building up a list of the tables and columns, maybe printing out an ERD (possibly one generated by Database Diagrams in SQL,) or would you pick a table and start there?

I know everyone likely has their own way of building up a DB and tables, but I'm betting there's at least some consistency when re-building a DB to make it more efficient / normalized (at least when you also have access to / control of the application itself)

Thanks,
Jason A.

Post #1377154

bitbucket-25253

Posted Thursday, October 25, 2012 11:45 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 5,101, Visits: 20,200

When designing a data base I always start in a manner that most think is backwards. That is I ask and ask again what does the user(s) need or want from the collected data.

How much information is each user allowed to access, for example a minor clerk should be able to see e-email regular postal mail addresses but NOT customer account numbers.

Knowing that, I begin thinking of how many tables and what each could / should hold ... what should be encoded/obscured (like account numbers / personnel data/ etc.) and from there .... normalizing the structure ..

Then before creating any part of the DB, I write a description as specific as possible for each level of user and have each dept/group supervisor sign off on it indicating I correctly understand that departments / group(s) needs.

Once that task has been completed, then move on into the actual DB design. What tables / what each table will hold ... and on and on. Then that outline goes to the developers for their comments and how difficult it will be for them to develope the interface for each level of user.

It is not as cut and dried as I have stated above, it is more give and take and revise, revise and revise yet again.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #1377175

« Prev Topic | Next Topic »

Permissions