Starting a database-driven website

[Drewstre]

Hiya-
I would like to start a website that will be primarily database-driven. I guess that's what it's called, I honestly have no idea where to begin. I do know that I will have a database of information, and I need that information to searchable by the folks accessing my site. Apparently SQL is the way to go. I'm somewhat familiar with consumer databases, going back to the Filemaker days, fiddling with OpenOffice these days, nothing serious.

Eventually, I would like for my website visitors to be able to submit their own data, which will be added to the main database upon review. But for now I'd like to start with a couple hundred entries, which will be constantly added to by myself.

What is the best way to start building the database that I need to be accessible (assuming SQL is the way to go)? I have the information, I just need to know what software to use to actually build the database itself. I apparently need some RDBMS software. MySQL starts at 2 grand per year. Crikey. MS Access is cheaper, but is that the way to go? Any free alternatives, just to get going?

Also, as far as web registrars, a friend recommended http://www.1and1.com. They seem to have a decent deal going right now... I would like to possibly monetize this site in the future; is there any difference between registering a site to host cat pictures and registering a site to host a for-profit business?

I have many more questions, but those are my tops for now. Any and all help is most appreciated.

Thanks,
Drewstre

[Evaders99]

Pretty much every host provides PHP+mysql for use. No extra charge. If all you want is a database-driven site, I recommend a framework like Symfony

[just brew it!]

MySQL only costs money if you want a support contract from Oracle. As already noted, it is free with many hosting plans (or with any Linux distro, if you're rolling your own server setup).

PostgreSQL is another excellent free (Open Source) RDBMS.

Access is a train wreck. If you insist on using MS for some reason, use MS SQL Server; it's actually pretty decent (there used to be a "lite" version of it that was free, don't know if this is still the case).

[wibeasley]

Do you have experience with any programming language or environment? Or are you starting from scratch?

Almost everyone supports JBI's advice to avoid Access in this scenario. If you go Microsoft, Sql Server Express is free. It's kind of the hook, in the hopes that your site's traffic will grow, and you'll need to pay for the better versions. They make the transition easy because the code & connections strings wouldn't need to change.

But it's unlikely that you'll be installing either. if you're paying a company to host your site and database.

I've never used Drupal, but it popped in my head during your description.

[Drewstre]

Thanks guys.
I am a greenhorn when it comes to all this... starting from raw scratch. But I really want to make this happen. I guess what I envisioned is that I would launch a database application on my computer, create a database, and fill it with a couple hundred entries. Then I would upload that database to my website. When I make changes, I would then either edit the one on the site, or edit it locally and sync it. So what program do I need to create and edit the database? That's the first baby step I need to make.

Thanks for your help,
Drewstre

[wibeasley]

I guess what I envisioned is that I would launch a database application on my computer, create a database, and fill it with a couple hundred entries. Then I would upload that database to my website. When I make changes, I would then either edit the one on the site, or edit it locally and sync it.

That's one approach, but the syncronizing can get messy, especially if updated records conflict. To make things easier during initial development, consider using just the host's database server (instead of an extra one at home). You'll be able to update it from home without noticeable latency.

When things get more stable, you'll need to add complexity to your setup so that you can continue developing/modifying the database schema, without risking the health of your live system.

So what program do I need to create and edit the database? That's the first baby step I need to make.

Two big decisions you'll need to research and decide first. First, do you want to use an existing framework that covers a lot of the plumbing (like symfony and drupal), or do you want to develop from scratch . If it's the latter, you'll need to decide between the LAMP stack or the Microsoft products (eg, SQL Server + ASP.NET w/ C#).

Of course these two dimensions are a little more continuous than I'm making it sound, but those are the two big forks that I see. I'd spend some time reading about those things to see which one feels appropriate for your application and your skills. Things like symfony and Drupal should have some tutorials that helps provide a feel. Be aware that they typically select usage cases that are best suited for their software, and your specific application probably won't fit as cleanly into their approach as their examples.

[pikaporeon]

MySQL only costs money if you want a support contract from Oracle. As already noted, it is free with many hosting plans (or with any Linux distro, if you're rolling your own server setup).

PostgreSQL is another excellent free (Open Source) RDBMS.

Access is a train wreck. If you insist on using MS for some reason, use MS SQL Server; it's actually pretty decent (there used to be a "lite" version of it that was free, don't know if this is still the case).

SQL Express isn't very nice with multiple simultanious users, so while I'm a SQL Server guy mostly, I would not reccomend the express edition

Both MySQL and Postgres are solid (free) options - I'm just becoming exposed to sqlite but not really sure what it can do, I only really have it to practice with [/very junior enterprise dba]

[redavni]

LightSwitch would be familiar to a Filemaker user. Filemaker is a bad word though, dont say that again heh.

You either need to talk to a web developer or become one. The decisions you are focusing on (database, hosting) are not the most important ones at this point. If all you need to do is get some basic data entry done to start you going, just head over to google docs, create a form and have at it. The results are saved to a spreadsheet that you can download in plenty of standard importable formats once you get to the point of deciding what actual platform your web site is going to run on.

[cheesyking]

As many have commented mysql and php are the classic things to use when writing a db driven website, however...

If you're not all that familiar with SQL you could look at some of the many no-sql databases. I've been playing around with couchdb recently and I'm just amazed at how good it is. It's VERY different from traditional relational databases (like mysql, access, mssql etc) but much easier to get started with and modify over time.

Rather than chopping your data up into tables you store related bits of information together in "documents". Say you were going to do an invoicing database in a relational db... You'd create a table for customers with a name and an ID etc. Invoices in another table with perhaps yet another table for the items on the invoice. You'd then need a bit of sql to pull all those bits of data together to display the invoice. Doing that in couchdb you just put all the info together in a single document.

Couchdb isn't suitable for everything but it might be worth consideration especially when you realise you can write your entire site only knowing HTML and javascript with the whole thing running direct from your database (since the couchdb server is actually a webserver itself the pages on your site can become documents in the database (this is called a couchApp)). Again, depending on what your doing this isn't necessarily a good idea.

Then I would upload that database to my website. When I make changes, I would then either edit the one on the site, or edit it locally and sync

This is actually built into couch, just provide it with the addresses 2 dbs and they sync, you can even have both dbs being modified and then sync them together (conflicts still have to be resolved manually so use with caution, you'd probably also want to create a VPN or similar to secure this).

To get started with couch just download it http://apache.mirrors.timporter.net/couchdb/packages/win32/1.2.0/setup-couchdb-1.2.0_otp_R15B.exe, install and run it, then point your web browser to http://127.0.0.1:5984/_utils. Create a db called test and add a document to it. Then add a field to that document called "hello" then set its value to be "world". Save the document and copy the bit like this from the end of the address in your browser: "test/f2bdbfe8b2b48a064584747a7800081c". Now open a new tab and use that stuff you just copied to build an address that looks like this: "http://127.0.0.1:5984/test/f2bdbfe8b2b48a064584747a7800081c". Something like this should display in your web browser

Code: Select all

{"_id":"f2bdbfe8b2b48a064584747a7800081c","_rev":"1-15f65339921e497348be384867bb940f","hello":"world"}

You just created a db driven website... sort of.

Moving away from the database stuff I'm also going to stick my neck out and rubbish php. I know everyone uses it and there are a million and one existing scripts out there written in it but it's a horrible language. Starting from scratch it wouldn't be my first choice of language if for no other reason than having to put $ and ; and -> everywhere is tedious and fiddly. Switching to python was the best decision I've ever made. The only reason I'd use php is if I already knew how to use it or there was an existing project you could easily modify to suite my needs.

One of the biggest drawbacks with doing something a bit radical like couch or python this is that you wouldn't have so much choice in who you got to do the hosting.

RE 1and1, personally I hate them for their crappy DNS control panel but they are cheap and there are worse people out there. Of course you need to write the site first so no point worrying about hosting yet. You can install all the server stuff on your desktop to do the actual development work.

It's also worth pointing out that if you don't have much (any) programming experience then you are very much trying to run before you can walk, "it ain't like dusting crops boy".

[Drewstre]

Thanks to you all for the quick and informative responses. Much obliged.

To make things easier during initial development, consider using just the host's database server (instead of an extra one at home). You'll be able to update it from home without noticeable latency.

So, that means, I think, that I would access the database entirely through a web-based interface to my database, which is hosted entirely on my registrar's servers. I'm good with that. That seems like a good reason to settle on a registrar early...

First, do you want to use an existing framework that covers a lot of the plumbing (like symfony and drupal), or do you want to develop from scratch.

Definitely _not_ gonna roll my own, especially at these formative stages, and given my own inexperience. The database I want to make is pretty standard fare. Let's say the site I want to make is a database for serious coin-collecting enthusiasts. I want to populate the initial database with every scintilla of information on the coins I already have information on. I want my users to be able to search my database, using any possible aspect(s). There will be no storefront or anything like that. (for now MUWAHAHA. sorry). Really just basic search and display, with a nice interface (which I figure I can build once I have the database itself planted).

Both MySQL and Postgres are solid (free) options

I looked at Postgres, and to my untrained eye, it looks very capable. It is, as you say, free. But when I go to http://www.mysql.com/products/, it looks like they want 2 grand annually. Am I looking in the wrong place? Is this rendered moot by my response to wibeasley about the database being hosted and accessed remotely?

If all you need to do is get some basic data entry done to start you going, just head over to google docs, create a form and have at it. The results are saved to a spreadsheet that you can download in plenty of standard importable formats once you get to the point of deciding what actual platform your web site is going to run on.

That's starting to sound like where I'd like to start. But will I be able to migrate that data to, say, the SQL database hosted by my web host?

One of the biggest drawbacks with doing something a bit radical like couch or python this is that you wouldn't have so much choice in who you got to do the hosting.

This all harkens back to my choice of registrar, which brings up another concern: I'd like to register my domain (both .com and .net) as soon as possible. I'm honestly kind of surprised it is still available. I'm not sold on 1&1, but my clicking finger has hovered over the "Buy Now" button more than once, on a couple of different registrars, pending advice from knowledgeable folks like you guys and gals. I would really appreciate any input you folks might have as far as a proper, dependable registrar to go with, knowing what you know of my intentions and needs.

Thanks again to you all for your help. I'm getting pretty psyched, and any suggestions you may have, however basic or advanced, are greatly appreciated.
Drewstre

[wibeasley]

Is this rendered moot by my response to wibeasley about the database being hosted and accessed remotely?

Correct, at that point, the only database software that you'd need to own is the frontend/client software to administrate the schema and stuff. All the databases you're currently considering should have decent front-ends for free. Double-check once you decide on one though.

But will I be able to migrate that data to, say, the SQL database hosted by my web host?

I've never used Google Docs, but I don't think they have a database equivalent (or something similar to MS Access). I think that means you'd store your information in a spreadsheet. Theoretically you should be able to export that to any database. But typically spreadsheets attract a lot of user mistakes, because they don't force a structure on the responses. Or something occurs where they less-structured data doesn't map cleanly to a well-organized database schema. Maybe "Google Docs Forms" helps prevent some inconsistencies. But I'd be surprised if it is as effective as a thoughtful & controlled web-front end client.

This all harkens back to my choice of registrar, which brings up another concern: I'd like to register my domain (both .com and .net) as soon as possible. I'm honestly kind of surprised it is still available.

If it's a big concern, you could register the site now, and then choose a (possibly different) host in a week or two. I've had a good experience with Dotster. There's a long thread on this topic somewhere in the TR forums.

[Flatland_Spider]

Moving away from the database stuff I'm also going to stick my neck out and rubbish php. I know everyone uses it and there are a million and one existing scripts out there written in it but it's a horrible language. Switching to python was the best decision I've ever made.

I agree that PHP is a bad language, but because of mod_php, it integrates well with Apache. Python uses the CGI/FastCGI function, which isn't quite as nice or as easy.

Don't get me wrong, I like Python, and I've looked into it as a way to get into web programming, but PHP just has the better infrastructure.

To make things easier during initial development, consider using just the host's database server (instead of an extra one at home). You'll be able to update it from home without noticeable latency.

So, that means, I think, that I would access the database entirely through a web-based interface to my database, which is hosted entirely on my registrar's servers. I'm good with that. That seems like a good reason to settle on a registrar early...

You would access the Db entirely through the web application that you use. You would create a form that adds data to the database and interact with it that way.

Both MySQL and Postgres are solid (free) options

I looked at Postgres, and to my untrained eye, it looks very capable. It is, as you say, free. But when I go to http://www.mysql.com/products/, it looks like they want 2 grand annually. Am I looking in the wrong place? Is this rendered moot by my response to wibeasley about the database being hosted and accessed remotely?

Most people get MySQL from their webhost or from the repos of Linux distro they use. The community edition of MySQL is free, and that is the one you'll want to download, if you decide to use MySQL. Here is the link to the community edition. http://www.mysql.com/downloads/mysql/

This all harkens back to my choice of registrar, which brings up another concern: I'd like to register my domain (both .com and .net) as soon as possible. I'm honestly kind of surprised it is still available. I'm not sold on 1&1, but my clicking finger has hovered over the "Buy Now" button more than once, on a couple of different registrars, pending advice from knowledgeable folks like you guys and gals. I would really appreciate any input you folks might have as far as a proper, dependable registrar to go with, knowing what you know of my intentions and needs.

The technologies used affects your choice of hosting provider rather then registrar. You can register with which ever registrar you like then point the DNS to your host's server. You can also transfer between registrars, so you're not locked into just one.

I like Dreamhost as a registrar and hosting provider, and Name.com as just a registrar.

You should definitely buy the domains as soon as possible. You can never tell what's going to happen.

[redavni]

If all you need to do is get some basic data entry done to start you going, just head over to google docs, create a form and have at it. The results are saved to a spreadsheet that you can download in plenty of standard importable formats once you get to the point of deciding what actual platform your web site is going to run on.

That's starting to sound like where I'd like to start. But will I be able to migrate that data to, say, the SQL database hosted by my web host?

Technically it's a a very common wizard based process. It literally takes less than a minute from google doc to sql server. Google Docs just a way to get started doing something without spending a few weeks getting familiar with whatever environment you choose. If you choose the Google Cloud/Android then you might get a headstart, but however you do it, you gotta have data in a database. Visual Studio will generate an entire barebones .Net data entry tool automatically given a database in minutes too.

If you are personally going to be delving deep into managing the database and writing front end code for data entry for your users on the web, then you should get a handle on what platform you want to develop with. There are so many choices these days. Microsoft, Linux, Google, Apple, and a hundred smaller players. Whose ecosystem are you going to dive into? This will help define your tools. If this is your first pass at a website, you are going to end up redoing it anyway, multiple times a night. I'd just pick the solution that gets me from point A to point B.

Calling a consultant or try to find a local user group are always good options.

[Kurotetsu]

As far as web hosts go I would recommend Nearly Free Speech. You pay for what you use, and costs decrease as you consume more resources. I run a small static website and I essentially pay nothing once the bandwidth costs are tabulated (its something like $0.000000012). I'm looking to move over to an active database-driven site like you and it only costs $0.02/day for a MySQL process ($7.30/year), and you're not limited to MySQL either.

[DLHM]

I would get a hosted site from godaddy or whtever and install Wordpress. http://wordpress.org/
It is very easy to use, the database is sql and is in the backend. find some plugins to help do exactly what you want,( i'm sure you can find one)..

[ludi]

I would get a hosted site from godaddy or whtever and install Wordpress. http://wordpress.org/
It is very easy to use, the database is sql and is in the backend. find some plugins to help do exactly what you want,( i'm sure you can find one)..

Personally, I wouldn't touch GoDaddy with a fresh tetanus shot and a ten-foot flaming pole. There are plenty of small hosts that charge better rates and will give you high-quality customer service, because they didn't blow away their entire annual operating budget by condensing the Girls of Craigslist into a Superbowl advertisement.

In my particular case I use Nefirms as my registrar and HostIGR as the site host, but there are lots of options.

[Captain Ned]

Personally, I wouldn't touch GoDaddy with a fresh tetanus shot and a ten-foot flaming pole.

Ditto. When in the day job I see a "mortgage broker" whose site is registered through GoDaddy, I know it's a scam artist.

[DLHM]

I have tried a few other hosting companies and found a few that completely suck. Go daddy only sucks because it's slow, other than that it's very easy to create database and install software. I've had no problems with them. They could make a better interface with though. Their site is way too busy.

[Drewstre]

I appreciate all the help folks.

I'm gonna check out Dreamhost and Dotster, as well as NearlyFreeSpech, and see what kind of deals I can get. Heh, I may be a n00b, but even I knew to stay away from GoDaddy. ("Hmmm, how do we sell more websites to respectable businesses? Oh I know! Boobs!" Seems legit.)

Calling a consultant or try to find a local user group are always good options.

That's a good idea. I do have a friend with some experience; I think between the two of us we can get me on stable ground. I'll also look into local groups.

I would get a hosted site from godaddy or whtever and install Wordpress. http://wordpress.org/
It is very easy to use, the database is sql and is in the backend. find some plugins to help do exactly what you want,( i'm sure you can find one)..

Hmmm, interesting. I thought that was simply blogging software, I didn't know about it's database capabilities. Thanks!

Most people get MySQL from their webhost or from the repos of Linux distro they use. The community edition of MySQL is free, and that is the one you'll want to download, if you decide to use MySQL. Here is the link to the community edition. http://www.mysql.com/downloads/mysql/

Excellent. Thanks for the link!

----------
I knew going in that I have an uphill battle ahead. I'm relying, possibly too heavily, on my 30 years of computer experience to ease my break into this brave new world of website creation and management. But I believe my concept will be worth the struggle, and having the advice of more knowledgeable folks willing to take the time to share their experiences will make all the difference. Thanks. Worst case scenario, I'll have the experience of a failure to share with others, but I fully expect my eventual interview in Web Gods Magazine to be of a far more positive tack.

I've got some homework to do before that interview.

My idea is not absolutely unique; similar, ostensibly inferior things exist around the tubes. There are many like it, but this one is mine.

I'll have more questions, surely, and will of course post here when I'm live.

Thanks
Drewstre

[Flatland_Spider]

I would get a hosted site from godaddy or whtever and install Wordpress. http://wordpress.org/
It is very easy to use, the database is sql and is in the backend. find some plugins to help do exactly what you want,( i'm sure you can find one)..

Hmmm, interesting. I thought that was simply blogging software, I didn't know about it's database capabilities.

Most websites are database driven. Very few of them are static pages or written in a way that doesn't use a Db to store information, Dokuwiki and Get Simple CMS are two examples that don't use Dbs. If you install Wordpress, or whatever, and start looking at the fields in the database tables, you'll see where the content is actually stored.

It's kind of an odd concept, and it's not really what RDBMSs were designed for. It's this weirdness that is the main driver behind the No-SQL movement.