SQL Database Structure

[Joe Lemmer]

Hi there,

I hope it's OK to ask a purely SQL question here (I am using Java in the application as a whole). I want to create lots of lists of strings in various different categories and store them in a mySQL database.

I thought the way I'd do it is have just one table, and then have a column for each different category of strings. But each of my categories will have different numbers of strings to store, so maybe thats not such a good idea after all.

Do you think I should have a different table for each category of strings?

Is there an easy SQL command whereby I could add a string to the bottom of one single column of a table, without putting anything in the others?

Thanks

Joe

[Jimmy Clark]

I want to create lots of lists of strings in various different categories and store them in a mySQL database.
...
Do you think I should have a different table for each category of strings?

Sure.

Is there an easy SQL command whereby I could add a string to the bottom of one single column of a table, without putting anything in the others?

Yes, in general. An INSERT or UPDATE command will handle this. There are different dialects of SQL. I am sure that Transact-SQL will do this. Not sure about the others.

[Joe Lemmer]

Hi James,

Thanks very much for your reply!

I'd just like to ask a couple more questions:

I have created a new little table with a couple of columns and used a few insert statements to add data to just one column at a time, as I would if I were just adding a single string to a category (column) in my table via my GUI.

I added two strings (using two separate insert statements) to the first column, then I added a string (using a third insert statement) to the second. When I checked to see how the table was arranged using the 'SELECT * FROM my_table;' command it showed three rows with a string in one column and NULL in the other. What I was hoping for was that was that I wouldn't have any NULL's, and that I'd have just two rows because I wanted the last string to be inserted not on a new row, but at the top of it's column. Boo Hoo!

Is there a way to insert single pieces of data as I want to with mySQL?

If not, then I will just create a talbe for each category. No problem. Except... as part of my application, I want to get the names of the tables returned to a method in one of my classes. So I've had a trawl of the java.SQL class and think I need to get a DatabaseMetaData object and then query that to get the table names. But the parameters for the getTables methods look like it wants info that I don't know how to give. Catalog? schemaPattern? tableNamePattern? types[]?

I'd be really grateful if you let me know of another method or way I could get the table names of my database returned.

I can't believe you've read all that waffle!

Many thanks

Joe

[Jeanne Boyarsky]

Joe,
So you have

col1  col2
data1  null
data2  null
data3  null

and wanted

col1  col2
data1  data3
data2  data3

Is this correct? It's certainly possible to do. You would want an update statement rather than an insert for the third time because you do not want a new row. Insert = new row, update = change existing rows

[Joe Lemmer]

Hi Jeanne

Thanks for your reply:-)

The table structure I had was:

col1  col2
data1  null
data2  null
null   data3

and I wanted to be able to choose my column and for it to be automatically added at the end of my chosen column.

as in from:

col1  col2
data1  null
data2  null

to:

col1   col2
data1  data3
data2  null

Would I have to do something like make a query that:
1. Finds the first null row in the column that I want to add data to.
2. Returns this row number to another piece of code
3. Makes an update to insert the data at that first null row.

?

This might be a bit complicated for me, because I would have to test all the other rows to check their length, so that I would know whether I could use update to add my data, or whether I should use insert because another row was needed.

My SQL is not very good, so I'm not sure if this is correct and would be grateful for some guidance.

Also, I'd really love to know how to get the table names of a DatabaseMetaData object. I don't know what catalogs and schemaPatterns or tableNamePatterns are though (the parameters of getTables method in the DatabaseMetaData class). This would mean that I have the option of just using different tables for each category of data.

Thanks again

Joe

[Jeanne Boyarsky]

"Finds the first null row in the column that I want to add data to."
How do you define "first". The first one alphabetically? The first one in the order the records happen to be stored in the database?

Your description shows you recognize you need two queries (insert vs update) for different cases. Since you are using JDBC, you can use Java to help you.
1) Do query that determines key of row to update (or no records if an insert is needed)
2) Do insert or update query as appropriate

[Jimmy Clark]

I'd be really grateful if you let me know of another method or way I could get the table names of my database returned.

Store a list of the table names in a table and give each table name an unique id.

DATABASE.META(ID, TABLE_NAME)
[ November 30, 2008: Message edited by: Jamees Clark ]

[Joe Lemmer]

Thanks James. Thanks Jeanne. ;-)