Really need some help - SQL issues.

fluiduk · 31 Oct 2006 at 15:52

Hi All

Working on our new website at work and we have hit a major snag.

We were going use MS Sql server 2005 Workgroup edition, however we have just found out that it doesnt do what we need, turns out we need Standard Edition which is 5k, we simply cant afford this at this time.

If I list what we need to do below, and how I am thinking of doing it could you guys give me some feedback?

We have multiple product feeds from multiple suppliers that are uploaded onto our server as csv feeds via FTP.

These feeds need to be inserted into a table, 1 for each feed, ie feed a > table a etc etc.

These then need to be inserted into a master table, along with a field that lists what feed they came from added to the end.

This table then becomes our master table which feeds the rest of the site.

The issue is automating the procedure. Do you the whole thing could be done in Mysql and PHP with cron jobs?

The next snag is that our site is currently running on asp. Could we use this to work with MYsql, or would it be better to insert the data into an SQL Database, probably MSSql Express Edition

Thanks for any feedback guys, my heads a mess

Aaron

JIMA · 31 Oct 2006 at 22:51

If you're after an OpenSource relational database have a look at Ingres. It's OpenSource now (previously sold by Computer Associates), runs on Windows or UNIX and can handle very large amounts of data. Although it's OpenSource support would probably cost a little bit to a lot depending on what you wanted.

Ingres drivers exist for a wide range of different languages so that wouldn't be a problem. For example, I've had Ingres working with UNIX shell scripts, C, Perl and Java.

Reading flat files into Ingres tables is extremely easy (using the copydb statement). The form of SQL that Ingres uses is pretty standard as well so is straightfoward enough to learn. There are loads of other good things about it such as database procedures, events etc.

I'm not saying that Ingres is better than other databases, is easier to use, can be used from more environments or offers more features. But I am saying that it's definitely worth a look as, over the last 12 or so years, I've found it pretty decent.

fluiduk · 2 Nov 2006 at 11:36

Thanks for that

Anyone else got any feedback

Ta

rpstewart · 2 Nov 2006 at 14:33

What you're looking to do is classic data warehousing stuff, any database worth it's salt should be capable of handling that. The problem is choosing which one given that you're looking for freeware/open source.

IBM's DB2 Express is now free I believe and will do everything you're looking to do from a data management perspective although I'm not sure how well it'll deal with asps. The other problem is the learning curve is cliff like at best - I've been using DB2 for 8 years plus and there are still bits where I get lost.