Database schema document creator

[Ruby the Liberal]

I am in the market for a CRM system and want to create a database schema in advance so that I can document the fields I need as well as the relationships to make sure that I can setup/modify whatever is out there to compare.

Any recommendations on an application that will allow me to do that?

Don't really need the full monty (data field types, etc) as I am the only one that will be using it as a reference - just a table/field chart showing relationships - I'll know by the field names what type of data is being captured.

Help?

[ChromeFoundry]

1. Couple of options...

You could use Access if you have MS Office.
A free alternative is OpenOffice Base.

Each one of these will allow you to build out a mock schema and apply indexes, primary keys and table joins. Both should also allow you to build something similar to a stored procedures, views and user defined functions. Either app will allow you to upsize to a full RDBMS in the future, like MySql or MS SQL Server.

[Ruby the Liberal]

2. Thanks CF

I do have Access here and MySQL server is installed - which both have the documentation utility after the fact - will check to see if I can use one of those to just pound out a schema without having to actually create the tables. I can create the database, but just really don't want to fool with building a UI or fussing with writing raw SQL queries as opposed to a canned reporting engine, so looking for a commercial application that is modifiable to accomodate some kinda complex nested relationships.

I will poke around with those to see if I can get a quick and dirty requirements doc put together. Doesn't need to be pretty (and no stored procs or functions needed), as it is only for my use, but I visually process relationships better in a schema than a list of needs in a word doc.

All of the data I need to capture is in my head (and notepad) right now. I'll take a look and see if I can make a diagram out of those options without having to build the actual tables to produce it. If I need to do that to make a reference doc, its just an afternoon, right?

I used to have a REALLY cludgy schema creator in school. Hated that app with the heat of 1,000 suns, but am kicking myself now for deleting it.



Thanks!

[ChromeFoundry]

3. MS Access has a database designer

so you can graphically create a database in a convenient UI driven tool. Foreign keys are as easy as dragging a link between the two tables, and you can print out the schema in its graphical representation.

MS SQL has the same, but I don't think SQL Server Management Studio comes with the free Express version. There are some other options if you want to use MS SQL Express:
TOAD for SQL Server - http://toadforsqlserver.com/index.jspa
Versabanq Squel - http://versabanq.com/products/squel.php
LinqPad (if you are a .Net dev) - http://www.linqpad.net/
Query ExPlus - http://sourceforge.net/projects/queryexplus/
SQuirreL SQL - http://www.squirrelsql.org/index.php?page=screenshots

For MySQL you have a lot of options too:
MySql Control Center - http://sourceforge.net/projects/mysqlcc/?source=directory

There are many SQL CRUD procedure generators out there on both CodePlex and SourceForge.

Best of luck,
-CF