Participant

Question

How do you create a database

Forum|Forum|19 years ago
January 29, 2007
12 replies
3426 views

How do I create a database? I have microsoft access installed but I read somewhere that, if possible, it is better to use some other program to build a database (something about if I use access then it uses my computer as opposed to my webhosted database).

I have also installed microsoft server express 2005 or whatever it is called. I have bought the book Sams teach yourself SQL in 10 mins. It is easy enough to understand but it is teaching sql language to make use of the database, but I don't know how to create a database....

Please tell me if it is ok to use access and if there is any book specifically about building databases or something like that.

Is there any way to create databases in access, upload them to the webhost that I am using ..... will that work? Please let me know how it all ties in together..

Getting started

This topic has been closed for replies.

D

Dinghus

Inspiring

Actually let me back up. If you DELETE your database you remove the ODBC connection also. If you just overwrite the db then you don't. But then there is normally no need or reason to DELETE a db before uploading the new version. Especially if it is a live db. You could lose data that way.

Of course I have seen dbs that were so messed up that deleting them was the kindest thing I could do for them. Put them out of their misery.

Database Design for Mere Mortals is one I recommend to everybody. I review it every so often just to keep myself on the right track. Lot of work sometimes but it is worth it in the end. Of course he sticks with how I was originally taught to design dbs way back when your db was a stack of punch cards so it just reinforces everything.

tclaremont

Inspiring

I made the mistake of NOT getting a solid foundation in database design before developing a lot of my sites. I have regretted it many times over.

At this point I grab every opportunity to build on my database design skills.

And now, thanks to Dinghus, I just bought the book he mentions above, for 8.50 on Amazon (used).

Even if I look through it once a month it has to be worth $8.50!!!

tclaremont

Inspiring

I think I vaguely remember having to recreate the ODBC connection if you did something like the following:

Say you have an Access database in use with a hosting provider.

You have a local copy of the database that you make some changes to.

You now want to replace the host copy with your local copy.

If you DELETE the host copy, and then UPLOAD your local copy, you THEN need to recreate the datasource.

This is going back many years at this point. Does that ring a bell with anyone???

A

azadisg

Inspiring

quote:

Originally posted by: tclaremont
...
If you DELETE the host copy, and then UPLOAD your local copy, you THEN need to recreate the datasource.
This is going back many years at this point. Does that ring a bell with anyone???

I vaguely remember something like that, though I think since CF6.1 even that did not call for re-creation of ODBC connections...

D

Dinghus

Inspiring

What I've run into is things like embedded queries don't work, tho somebody told me that was fixed in the newest version. Just doesn't meet ANSI standards. Time after time simple queries that are doable in even Access won't work in MySQL. Of course from what I understand now v5 has fixed a lot of the problems and even has some limited stored procedure ability but that still has a ways to go too it seems.

Anyway, when you say that you have to recreate the ODBC connection, what do you mean? You mean like in ASP, .NET etc etc where you have to open a connection to the db everytime you want to use it but in CF you don't have to do that?

I've used CF since v4 and many people were using Access for dbs because it was the easiest thing to use at the time and never had any trouble with the ODBC needing to be redone everytime the db was reloaded. I can't even see any reason for it unless you changed the name or path. I think you had a bug or something for that to happen.

A

A3gis

Participating Frequently

quote:

Originally posted by: Dinghus
Anyway, when you say that you have to recreate the ODBC connection, what do you mean? You mean like in ASP, .NET etc etc where you have to open a connection to the db everytime you want to use it but in CF you don't have to do that?

I've used CF since v4 and many people were using Access for dbs because it was the easiest thing to use at the time and never had any trouble with the ODBC needing to be redone everytime the db was reloaded. I can't even see any reason for it unless you changed the name or path. I think you had a bug or something for that to happen.

Ditto on using CF since version 4 - but the issue was definately there, and not with deleteing the access DB first, just doing overwrites - it could've been a bug, after all i setup the NT server it was all running on... and I'd been a mac-user only for the 10 years prior =)

PS: sorry for hijacking your thread Jfuentes!

tclaremont

Inspiring

There are people dedicating their careers to database creation and administration. "Teach yourself SQL in 10 minutes" is not going to teach you everything you need to know.

It is entirely possible to set up a lousy, poorly performing database that will work with ColdFusion in a matter of minutes. It is quite another project if you want it to be fast, reliable, and easily maintainable.

You will get out of it what you put into it. How good do you want it to be?

tclaremont

Inspiring

There is more than one connection to an Access database if you have someone opening the database in Access at the same time a web user is accessing the database through CF.

D

Dinghus

Inspiring

Hmmmm. I hate to intrude with a reality check but when you use Access in a web environment you only have ONE connection, not muliple like when it is being used as an application itself.

Access is a nice way for newbies and small websites to have a database that is easy to create and manage. It does NOT require redoing the ODBC everytime you reload it. (where does this stuff come from?)

The nice thing is that a person can create the whole thing, test it, and then upload it right on their local computer without going thru cumbersome interfaces.

That being said, if you are looking for power etc then go with MS SQL. I highly recommend against MySQL. It has some flaws and lacks some features that will drive you nuts. Of course MSSQL will probably cost you some extra on your host, but yo could use godaddy where it won't.

A

A3gis

Participating Frequently

quote:

Originally posted by: Dinghus
Access is a nice way for newbies and small websites to have a database that is easy to create and manage. It does NOT require redoing the ODBC everytime you reload it. (where does this stuff come from?)

as i said in my post, it was years ago I used Access - it USED to require the ODBC connection be recreated in the ODBC control panel - this was before CF handled that side of things for you, and I've not used Access since. At that time we were using it for ASP and CF training and everytime a student/trainer modified one of the DBs and handed it to us to reupload to the server, the connection HAD to be recreated or it broke.

quote:

Originally posted by: Dinghus
That being said, if you are looking for power etc then go with MS SQL. I highly recommend against MySQL. It has some flaws and lacks some features that will drive you nuts. Of course MSSQL will probably cost you some extra on your host, but yo could use godaddy where it won't.

Just out of curiosity, which lacking features and flaws are you talking about here with MySQL (PS: not trying to get into a mysql v mssql debate) - we use both DBs extensively here and I know we dont do much more with the two apart from treat them as flat files, far from ideal, but what are the main lacking features? so far my only issue between the two DBs is mssql's lack of a decent equivalent to mysql's LIMIT clause.

J

Jfuentes12Author

Participant

Thanks for all of the replies everyone. I am trying to use "SQL Server Management Studio Express"... That should work right? I opened the program, right clicked on "databases" and selected "New Database".

I then named the database "agents" as I am creating this for a training website for insurance agents. That is about how far I got.

Forum poster "Dan Bracuk" recommended the following:

"If your sql book does not say much about data modelling, or how to design a database, you really want more reference material. If you google "data modelling" or "database normalization", you should find an online tutorial."

Is that the best and easiest advice on creating and managing databases? I am learning SQL which seems easy enough, it is just the database creation & management that is giving me trouble. What is the fastest way to learn that? I don't need to know all of the major intricacies just basic stuff.

Please let me know if mssql server express is ok to use, and or if anyone knows a website or book that has a very easy explanation of creating and managing databases... Thank you very much.

D

Dan_Bracuk

Inspiring

If your sql book does not say much about data modelling, or how to design a database, you really want more reference material. If you google "data modelling" or "database normalization", you should find an online tutorial.

tclaremont

Inspiring

If you are THAT new to creating databases, I would like to suggest that you avoid Access altogether. Start with MySQL or SQL Server. The reason I say this is because once you are up and running with Access, you will no doubt want to move to a more capable database anyway. Not only that, but some of the stuff you learn for Access will contradict or at least confuse you with regards to SQL databases.

Access is a desktop database, and not intended for multiple-use situations, even though you can "get by" with it in some cases.

It is just not a good idea.

L

LL20_2

Participating Frequently

Please see this TechNote: http://www.adobe.com/go/tn_17034