Need help with Database please

  gplatt2000 20:35 01 Jan 2004

Before I start, Im not sure if this should be in her or in the PC section, so sorry if Ive got it wrong.

I am inn the process of making a website, where companys can advertise their services in set spaces. These places have numbers, from 1 - 20. What I want is for advertisers to be able to choose their advert no., and then be taken to a new page to view the months when that ad is availabe as some sort of list (drawn from a database I have in access).

I then want the user to be able to choose the dates to advertise (sonething like checkboxes by each month), and the information sent to me to put into the database.

Also, I would like there to be a page after tey have chosen the dates saying something like 'You have chosen advert 6, for 4 months. This will cost £xx'. So I would needsome sort of way of the database working out the costs? Bearing in mind each ad no. has a different monthly rate.

I have tried a few ways I can think of in Access, but Ive still not had much practice, still waiting for books from the library. So if anyone has any idea please help me out.

Thanks a lot in advance


  Taran 21:14 01 Jan 2004

The cost caculation could be run using several methods, one of which is by getting your database to work everything out for you. A simpler method by far could be to use a series of programmed form fields (JavaScript, PHP, ASP or CGI would all work) that would calculate costs based on selections on a form then give the option to buy, followed by an order process.

I see a few potential issues here though, before we start getting to brass tacks.

For your system to work at all, your web server database has to be absolutely up to date or any new bookings could overlap existing bookings. This means it has to know when every customer bought his or her slot of ads, which space they occupy, when they run from and for how long since without this data nobody will know what is or is not available.

The only real way around this is to go into a small scale e-commerce operation, otherwise sooner or later you run the risk of someone tryng to buy a slot that has already been sold, only you haven't updated the online database to reflect that sale.

Whatever someone buys (advert position 10 for two months starting August, as an example) is charged to them, they pay for it, and your web server database updates on completion of the transaction to tell everyone else who may be interested in that slot that it has been sold and is no longer available.

It's not the sort of thing you cook up overnight and it isn't the kind of thing I can just say, "do x, y, z and all will be well".

The trouble with databases and data driven sites is that planning and designing your database is the key to it all working, or not, and going beyond simple queries on one or two tables of records to running appends based on purchases, creating tables or account holder records, details of processed orders (date, time, amount) and similar is where you can fall flat very quickly.

Have you considered setting up a manual system where the available dates and slots are posted by you as a web page ? Your customer selects from those options in a form and submits via email. On confirmation of availablity they pay you, and on completion of the transaction you update the list of availablity on the site, effectively removing the slot you have just processed from further selection by another customer.

It may sound cumbersome and it is when you compare it to a fully fledged e-commerce system. It relies on a lot of manual processing by you. Compared to learning database design along with programming the necessary scripts to effectively use your database, it suddenly may seem a more attractive option though.

To give you something coming close to useful help I would have to know the exact ins and outs of what it is you are selling, how you intend to process the orders (PayPal et al), will it be on a per month basis, will you offer discounts for long term bookings of 3 or 6 months (just as an example) and if so what percentage will the discount be ?

Even without all of this I can tell you that the likelihood of you being able to set your system up using Access without writing at least some maual VBA code is probably quite slim.

Data driven sites can turn into a serious nightmare and most of the problems originate in the underlying database design. I'd love to say it's dead easy and it will be a breeze, but it isn't unless you have a thorough understanding of two things: your intended selling process (what you sell, how long people buy an ad for, its position and so on) coupled with database design and implementation.

  Taran 21:35 01 Jan 2004

Do you by any chance have NetObjects Fusion ?

The reason I ask is that you could use some of its built in features to set up something workable that would be far simpler than trying to get your head around Access, not to mention the ASP scripting required to interact with the database.

NetObjects has some very useful features.

Since it has built in e-commerce capability that is about the easiest I've ever seen for setting up a cataloge of goods or services for sale and linking into a payment processor you could use that and be up and running quite easily.

Or you could even use its stacked page option to store and display your options to buy and then link your payment form into a commerce facility.

For e-commerce in NetObjects you click the E-Commerce Catalogue icon and follow the prompts. For general use it really is that simple, but for what you want to do you will have to play with it a bit.

To my mind it beats trying to wrestle Access into submission then try to ASP code your pages or port the Access database into MySQL to run PHP. NetObjects can be used with ASP or PHP, while we're on the subject.

With a 1&1 hosting account you get NetObjects 7 for free. Amazon UK have version 7 for £60 or you can buy the latest version 7.5 online for just over £100.

Other than by using FrontPage 2003 and some of its superb data source abilities, it's about the only way I can think of that could get a working system online for you without a lot of very large headaches.

Just a thought...

  gplatt2000 21:41 01 Jan 2004

Thanks a lot for your help once again Taran!

I have thought of the manual system before, and as I have a max. of 20 orders to process per month it sohuldnt be too herd to keep up to date - I cahnged my mind on this not knowing how difficult the other options would be to set up!

However, I think I still need a database, which will store all copmany's names, locations, descriptions and their categories for users to view, 'arrange-by' and search.

here is a more detailed description of what I intend to do:

A site for Mini enthusiasts with tech advice, photos etc to keep them interested - but the main feature is an online 'database' (sounds a bit silly saying that now!) of shops, clubs etc throughout the UK, with deatils of contact info and their services. The users will be able to search this 'database' or rearrange it by the different fields, depending what they want to see.

The companys will pay to have their compnay in the list. Pament (for now at least) will be by PayPal. Payment will be deducted all at once when they book their advert placements. And no special discounts - I want as simple a system as possible.

Thanks again for your great help, and i hope this info can give me more of an idea of what I am doing.

Thanks once more

  Forum Editor 10:08 02 Jan 2004

would definitely be the way to go if you're selling a product or service, and you can set one up without too much hassle - most good web hosts offer one of the popular applications - but be warned that they aren't cheap.

Having said that, the service you're selling is online advertising, and by the sound of it you'll probably have only a few options (1 month, 3 months, 6 months, 9 months or a year - that sort of thing). I advise you to keep it simple.

The best way to set that up would be to make a simple table, with the various options listed with their costs. Each option would have its own 'Pay now' PayPal button - which you get from the PayPal site. On the PayPal site you l;og in with your account details and go to a special button design page. There you input the various item options and PayPal generate HTML code for that particular item/cost. You paste the code intop your table cell, and there's your button. Do it for each of your options, and when someone clicks a button they'll be charged the price for that option. It's easier to do than to describe!

Now you'll have a neat online payment system - you can link the payment table to a form that would be completed by the company making the payment, and this can come to you using the 'mailto' script in your web host's cgi bin - easy to set up, and you can then tie up the form with the PayPal payment. It all works very well, I've done it many times for clients.

That would resolve the e-commerce side of things, but you'll still need to tackle this list of suppliers. If your site was running on a server that had Microsoft's Sharepoint technology installed it would be easy enough to do this with an Excel list, which would be searchable online. Use of this technology is still growing, and although it is extremely useful web hosts have tended to be slow to take it up - partly because of cost. You could make a list in a table, and sort the companies into categories. Then you could create bookmarks to the categories, accessible by hyperlinks on your page. When someone clicks on a category (or sub-category) the browser jumps to that section of the list. The actual list pages are 'hidden', that is, they aren't shown on the navigation buttons. This is an effective and easy method of providing some kind of sorting facility, and of course you can create as many tables as you like, with as many categories as you like - it works very well, provided you don't have thousands of entries.

Otherwise you're into databasing I'm afraid, and as Taran has already said, this isn't something that is a walk in the park, especially as you don't have any previous experience in the field. That isn't to say you couldn't do it, anyone can, given time, but you might find it quite a tussle to begin with.

  Forum Editor 10:13 02 Jan 2004

that PayPal are (at last) setting up a UK operation, so all UK customers will have their accounts dealt with locally. This is likely to happen in February, and all existing customers with a UK address will find that their accounts will be transferred here. There'll be no element of choice in this - if you don't want your account to be UK based you can say so, but you'll find yourself without an account at all.

  gplatt2000 12:03 02 Jan 2004

Thanks again for the help people.

More & more options! So confusing...

Taran - you mention 'by using FrontPage 2003 and some of its superb data source abilities' - I am thinking of getting this software - I presume you've used it?

Also, I have been considering 1&1 for hosting, so may also be able to use NetObjects. I'm going to have to have a longer think on all of this, and Ill get back to you.

And ForumEditer - I have thought of this system (a table with PayPal buttons) originally, because of its simplicity. BUT, this still means I would have the problem of possible booking overlaps - unless I decide to just go for doing the whole thing manually, which I am still tempted by.

However, as you have said that still leaves me wih the database of suppliers - your alternative suggestion sounds good - do you know of anywhere I can get more information on this?

Thanks again for all our help

  Forum Editor 12:32 02 Jan 2004

since it went into its beta stage, and I'm fairly sure the same applies to Taran.

It's the best version of FP yet - by a very long chalk, and it rivals anything else you can use. The great thing about FP 2003 is the way it can handle data sources, and in this respect it's probably the best application curently available. The same caveat applies though; if you're taking your first trip into database-land you're in for a pretty steep learning curve.

FrontPage has the ability to create the bookmarking scheme I mentioned earlier, so you'll have no problems in that direction. Once you've created your list of suppliers (do it in a table) you can create bookmarks very easily.

NetObjects Fusion has the ability to create an online store built in. It's a PayPal store, and it works in exactly the same way as the manual method I outlined above - although it looks a little different. The same peoblem will apply though - you'll have no way of preventing the overlaps you mention - the PayPal system will go on making sales until you remove the appropriate button from the site.

  gplatt2000 12:36 02 Jan 2004

Ive had a look at host supporting SharePoint - it seems very expensive compared to other hosts I have looked at - is this high price set to stay, and is it generally the same for all hosts - do you know of any cheapish hosts?

Thanks again

  Forum Editor 13:02 02 Jan 2004

it's not a cheap technology. You can get the basic SharePoint package for around £24 a month. That will typically include 50Mb server space (plenty unless your site is absolutely huge) plus MS SQL 2000 for your database - all ideal for a FrontPage site.

That's quite an expensive package, and the high cost of SharePoint is working against it - at least in the small business sector.

  gplatt2000 13:09 02 Jan 2004

Are there any other alternatives?

thanks once more

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?