SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jasona.work
jasona.work
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9968 Visits: 12526
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.
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15435 Visits: 25280
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search