Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Redesigning a database from scratch, where to start... Expand / Collapse
Posted Thursday, October 25, 2012 11:21 AM



Group: General Forum Members
Last Login: Thursday, December 1, 2016 2:17 PM
Points: 1,772, Visits: 10,711
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)

Jason A.
Post #1377154
Posted Thursday, October 25, 2012 11:45 AM



Group: General Forum Members
Last Login: Sunday, January 4, 2015 7:55 AM
Points: 5,333, Visits: 25,280
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.


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

Before posting a performance problem please read
Post #1377175
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse