database suggestion

[arioch]

I am about to establish a big metalband database in 3 rd. nf with a total of 19 tables and just found out that MySQL has some nasty limitations in regards to the use of keys, and can't insert or extract data from several tables at the same time.

I would like to be able to copy the primary key to the relevant subtables - unless just putting an auto incremented numberfield in each table and then join the tables together through them, is ok.

What's out there that's OSS and fits the bill? What about the database in OO2? It's supposed to be an old discarded IBM-thing as far as I know...

I am a complete database newbie and badly need a hint here.

Later.

[bwkaz]

and just found out that MySQL has some nasty limitations in regards to the use of keys,

What are they? It's done everything I needed it to do, for whatever that's worth.

and can't insert or extract data from several tables at the same time.

No, it can't insert data into multiple tables at once, but that's not needed anyway. (Just insert data into the primary table, then into the child table. Python's MySQL interface even has a way to get at the key-field value of the record you just inserted, if it's an autoincrement field. So you can use this to put the same value into the foreign-key fields in the child table.)

But it definitely can extract data from >1 table -- I've done inner, left outer, and right outer joins several times from it.

[arioch]

Thanx for responding.

I've been told that primary keys can't be copied to other tables as foreign keys.

"No, it can't insert data into multiple tables at once, but that's not needed anyway. (Just insert data into the primary table, then into the child table."

Not very practical when you have six or more separate tables under the primary table. I don't want to repeat the same process seven times in a row. I want to insert data once and be over with it, no matter how many tables are involved.

[bwkaz]

I've been told that primary keys can't be copied to other tables as foreign keys.

I believe that's wrong, because I've done it several times. But you have to be using the right language -- not all language bindings allow you to find out what the most-recent PK value was, so you can't copy that value into the child tables when you use some languages.

And I've never seen any relational database that allows you to insert into more than one table in one statement, for whatever that's worth.

[webwolf]

have a look at postgreSQL http://www.postgresql.org/
webwolf

[arioch]

I don't know if we're talking a bit "off" each other here, so allow me to elaborate: I just want to recieve userdata from an XHTML form using PHP, and have everything transported into the respective tables at just one push of a button at the bottom of the form. That really isn't possible with MySQL?

Doesn't the key capabilities also depend on whether you use InnoDB as the engine or not?

[bwkaz]

I don't know if we're talking a bit "off" each other here, so allow me to elaborate: I just want to recieve userdata from an XHTML form using PHP, and have everything transported into the respective tables at just one push of a button at the bottom of the form. That really isn't possible with MySQL?

Oh, that's what you meant. Of course it's possible.

You need to run multiple SQL "insert" statements in your PHP script, though, just like you'd have to do with any other database. (And note that PHP's default interface to MySQL is really rather poor -- for example, it lacks the ability to use parameters, and forces you to concatenate user-input with your SQL statement, which has inherent security problems -- Python's is much better. PHP has an alternative MySQL interface that's also much better (perhaps related to pear?).)

Doesn't the key capabilities also depend on whether you use InnoDB as the engine or not?

Yes, it does depend on your engine -- but then, why would you want to use flat files with no real indexing, anyway? (Since that's what the old non-InnoDB backend basically is.) You could use Berkeley DB as a backend also; that should support keys and whatnot, I think.

[arioch]

Ah, at least now we're on the same page... So, no problem with using MySQL as it is out of the box, since InnoDB is the standard engine.

"You need to run multiple SQL "insert" statements in your PHP script."

I sort of knew it would be something like that, but something I read on the net made me question it. I was told that PHPmyadmin couldn't make foreign keys because MySQL didn't have that capacity.

Could I get you to dig up the name of that PHP SQL interface improvement? Do you know if it also does away with the risk of these SQL injection attacks? That would be really neat.

[bwkaz]

It definitely helps with SQL injection, if the SQL driver itself uses parameters instead of just concatenating for you. The MySQL C API allows parameters, and the server will do the substitution "safely", at least since MySQL 4.1 (which may be why the default PHP interface doesn't use them).

From looking around at php.net, it appears that MySQLI is the interface I'd use. It has some of what the Python interface has, at least:

http://www.php.net/manual/en/ref.mysqli.php

It does allow parameters, but you have to specifically ask for them (i.e., use the prepare() function, then bind all the params; don't use the query() function):

http://www.php.net/manual/en/functio...li-prepare.php

It's still (IMO anyway) easier in Python:

cn = MySQLdb.connection(stuff...)
username="bob"    # Or take it from CGI...

cursor = cn.cursor()
cursor.execute("select * from users where username=%s", (username,))

for row in cursor.fetchall():
    print str(row[0]) + str(row[1])

(Or whatever. You get the point, I think. Basically, parameters are handled by passing an optional tuple into the execute method -- no parameters, no tuple required.)

[arioch]

Thanx for the info. Very valuable indeed.

If PHPmyadmin can't do foreign keys, can you name me an intuitive graphic app, that doesn't take too much study to start using, that can do this? Perhaps by drag'n'drop? What about OO2? Does it have that capacity? The new SimplyMEPIS is coming, up and it comes with OO2.

[bwkaz]

I've never set up a MySQL database with OO.o -- last time I tried setting up any database at all with it, it was the "internal" database format, and it routinely crashed (though that was version 1.9.100, not 2). I'm not sure you can even set up a MySQL database from OO itself.

What I used was mysql-administrator (not mysqladmin; this is actually a separate package). It didn't do foreign keys correctly either, though, so I had to do them manually. I kept a SQL script around for future reference:

mysql <whatever options>
ALTER TABLE `<database>`.`<table>` ADD CONSTRAINT `<pick a name>`
    FOREIGN KEY `<same name as you chose above>` (`<column name>` [, `<column name>`...])
    REFERENCES `<primary key table>` (`(null)`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

Instead of RESTRICT, you can use either CASCADE or some other option(s) I don't remember ATM -- check the MySQL manual for the full syntax of those clauses.

Note especially the (`(null)`) in the REFERENCES clause -- this makes the foreign key reference the primary key of <primary key table>.

Oh, and wherever I have angle brackets in there, remove them. They're just an indication that you need to substitute something.

[arioch]

I am seriously contemplating coding the database by hand now... i just need clarification on one subject. The actual joining of two tables. Consider the two example-tables below:

CREATE TABLE album(
  id CHAR(10) NOT NULL PRIMARY KEY,
  title VARCHAR(100),
  artist VARCHAR(100)
);
CREATE TABLE track(
  album CHAR(10),
  dsk INTEGER,
  posn INTEGER,
  song VARCHAR(255),
  FOREIGN KEY (album) REFERENCES album(id)
);

How would the join code look for these two tables? And what kind of join would be the one to use here?

This is my first dive into "SQL-by-hand", so in layman's terms please...

[aNoob]

You will most probably join them on id/album. Why don't you give the same name for the keys in your tables? Is much more easier to read. I would rewrite it like that

CREATE TABLE album(
album_id CHAR(10) NOT NULL PRIMARY KEY,
title VARCHAR(100),
artist VARCHAR(100)
);
CREATE TABLE track(
album_id CHAR(10) NOT NULL PRIMARY KEY,
track_id NOT NULL PRIMARY KEY,
dsk INTEGER ,
posn INTEGER,
song VARCHAR(255)
);

So for example, you have your Billy Idol Rebel Yell album and this one has number 5 as album_id and you want all the tracks from this album only:

SELECT * FROM track WHERE album_id = 5

I assume these are oversimplified tables because a track should have a name,duration etc. Unless you want to create another table track_details but then you should not hyper-normalize like that.
Well, if I said something stupid, please correct me.

[ooagentbender]

I've been using mysql and php alot lately and I think its a great solution to almost all database and webscripting needs. Just need to find out how to do it. good luck with your site and feel free to PM me with any other questions you come across aside from the ones you ask here.

[arioch]

aNoob:

1) On which fields would you join them? "Album_id" to "album_id", or "album_id" to "track_id"?

I am sitting here looking at my homecooked OO databasediagram getting awfully confused now, I'm not even sure the design isn't flawed now