Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Administration (2000)  Design/Dev Process Discussion (not a question)

Author  Topic  Jay99 468 Posts Posted - 2001-03-26 : 12:51:26 The majority of projects that I get staffed (unfortunately) on are small easy databases to backend some kind of dynamic web site. I see a lot of contact forms and dynamic content. I am wondering how ppl go from the requirements spec to designed/implemented database. Here is what I do. I have a skeleton directory structure that looks like this… Root --Bat --Constraints ----Foreign ------Create ------Drop ----Primary ------Create ------Drop ----Unique ------Create ------Drop --Database ----Create ----Drop --Defaults ----Create ----Drop --Indexes ----Create ----Drop --Sprocs ----Create ----Drop --Table ----Create ----Drop ----Grants ----Populate --Tools --Triggers ----Delete ----Insert ----Update --Views ----Create ----Drop {EDIT: Sorry, the tabs didn't come out in the above directory structure diagram . . .changed to spaces} {EDIT EDIT: Sorry again. Spaces didn't work either? changed to hyphens . . .} I usually take the requirements and draw an ERD in either Visio or regular paper. Once I have an ERD that I am happy with, I create a .sql for every object and put it in the appropriate directory. So in .\table\create is create_table_myTable.sql and in .\constraints\primary\create is create_pk_myTable.sql. Once I have labored through writing all those text files, I create a install_MyDB.bat and uninstall_MyDB.bat in .\bat that uses isql to run all the .sql files I have created. I can then create and drop my database from the command line until I have everything right. This process works pretty well for me. Especially since I work on so many small projects. When there is a problem, I can quickly go back to all my scripts and make a change and drop and recreate from script without having to do much thought. The one bottleneck is the process of going from a drawing to scripts. I don’t like the way Visio ‘generates scripts’. It is all in one file and doesn’t split out the constraints from the create table statement. I wish there was a way I could easily customize the way Visio did this. It is not uncommon for me to spend 8 hours (with my headphones on and my brain off) keying in .sql files. So how do you do it? Do you have everything in scripts or do you use the GUI? Do you script every object separately or do you have one big create script. Do you use any tools for ERD or do you design off the cuff? Discuss . . . Jay Jay Edited by - Jay99 on 03/26/2001 14:17:56 Edited by - Jay99 on 03/26/2001 14:20:48