[Solved] Maximum Number of Fields in 3.3

[MalM]

Hi,

I am creating a large database and I wanted to know if there is a maximum number of fields I could create in the table. I am about to run out of field spaces and I wanted to know if I should split up the database in two tables and create one form if that is possible?

Thanks

M

[r4zoli]

If you want to use OOo with large database forget odb file with built-in hsqldb database, because it is unstable with big tables. In your case better to use Base as front end to real database such as MySQL, Postgresql or Firebird.

No maximum number of fields for tables, you could learn database design first: http://www.sum-it.nl/cursus/dbdesign/english/index.php3

[MalM]

Hello,

Thanks for your input, however my usage is not that advance.

If you don't recommend OO for large databases, then is it possible to create several tables and link them to one form? If so, what are the steps to do this... Again, I am not verse in computer technical language, therefore you explanation has to be for a lay audience.

Thanks

M.

[r4zoli]

Detailed description you can read in Getting Started with Base.

[DACM]

I'm assuming some things in my response:
(1) You understand the basic concepts of a 'relational' database...
(2) You've considered database 'normalization' such that you actually require such a 'wide' Table.
(3) You'll consider creating “Indexes” -- because it's highly unlikely that you'll need to Search all Fields during Table queries. This can be critical for maintaining performance as you exceed even a few hundred Records/Rows when using the “Embedded database” configuration.
(4) And you're willing to employ some data protection -OR- someday follow r4zoli's advice and transition to a more stable database configuration.

You know this is a very good question because the Base GUI: Table Design View is limited to creating 128 Fields (Columns). Or I guess you could say this limit is 129 including an automatically generated Primary Key field using the associated Wizard. I presume this Field creation limit applies in all cases (through Table Design View), but I've only tested the default SDBC and various JDBC connections.

Some fun facts: The Base GUI creation limit has nothing to do with the number of Fields/Columns you can create, or even display, in Base through other means. I haven't found the display limit in Base, but the built-in database engine (HSQLDB 1.8.0.10) limit is theoretically 2^31 Fields per Table (yes, 2 billion Fields per table). Just to put that in perspective, that's about 1 million times the number of Columns per Table allowed in the average name-brand database. But then that's also the total number of addressable objects per Table (in HSQLDB 1.8). So the multiple of Columns*Rows*Bytes (average bytes per record) cannot exceed the same 2^31 byte limit due to the current Java-array limitations. So that translates into a maximum table size of 2GB in HSQLDB 1.8. Larger Tables will require an engine change, perhaps to HSQLDB 2.x.

Edit: I was testing for a Field/Column display limit in Base but soon stopped at 8192 Fields/Columns due to the extreme, but linear, drop in Table-display performance. One OpenOffice.org binary 'soffice.bin' grew to over 350MB in size during the several minutes it took to display the 'wide' Table with a single Row/Record. This testing reveals that 'wide' Tables are much less efficient for display purposes in Base than more modest designs with even millions of Rows/Records. So for performance reasons associated with table display in Base, it's probably not a good idea to use SQL to exceed the Base field/column creation-limit (128 columns or so). At least consider the efficiency of additional Tables in 1:1 relationship or other join possibilities.

Today's more advanced engines such as HSQLDB 2.x and H2 (both Java-based) support Large Object (LOB) data types which allow dedicated LOB Tables to reach 64TB (2^46) in size. Once again, that's about 16,000 times the LOB Table size limit of most name-brand database engines (without resorting to add-ons). And these massive LOB Tables do not degrade database performance because they employ place-holders in the reference table while automatically saving the actual LOB's to a dedicated table. So if your needs include media, then these newer Java engines are a prime choice. Otherwise, if you need ginormous Tables of non-LOB data (>2TB each; pretty hard to generate without an automated data logger) then it's definitely time to make a move to a name-brand engine.

Unfortunately, these facts about the built-in HSQLDB 1.8.0.10 engine can be misleading because the Base developers [evidently] asked themselves: “If I were designing a database application...what would it take to degrade one of the fastest, SQL database-engines in the world, such that performance drops exponentially with database size, even on the fastest computer hardware available today.” And through sheer determination, they succeeded, by intentionally reducing the cache memory setting well below the HSQLDB defaults and so low that a 1MB database becomes noticeably slow, while a 3MB database is virtually unusable due to performance lag. And what's worse, the performance lag translates into more time and reason for OOo to crash during the operation, thus destroying all your work through file corruption caused by Base bugs. How un-cool is that? Hence r4zoli's advice above. You can adjust the cache settings inside your .odb database file using a zip tool, but it's a bit of a hack. So before you start hacking your .odb files, you might as well add 'file' mode support to Base so you can avoid the data loss issues as well.

To be fair, the "Embedded database" configuration primarily targets users with modest database needs such as small DVD collections or an Address Book, so the performance issues shouldn't crop up in these everyday applications. But given the difficulty in using Base, we probably encounter most designers here in these forums. And we don't encounter many users interested in tracking DVD's or the like. So it may be that Base is increasingly considered for larger projects on par with the utility of MS Access or Visual FoxPro in the workplace. Base may be up for the task, but certainly not using the default .odb file stuffed with “embedded database” files. And as soon as you need to store (not just access from individual disk files) 'media' such as photos or even perform some advanced SQL operations, it's time to dump the legacy, built-in, database engine (HSQLDB 1.8). Along those lines, we've streamlined the move to HSQLDB 2.x or H2 with Base, because these tend to provide top-tier performance for up to million-record Tables, while lacking nothing in terms of SQL or desktop database features, even extending their reach into multi-user LAN environments. I'll mention that we now have several novices using Base+HSQLDB 2.x, so don't miss my signature links below if you're interested in that route; it's fully automated under Windows. But if your database is likely exceed a few million records per table, then the performance-edge shifts to the big-box solutions such as Base+MySQL and Base+PostgreSQL. So pick the best tool for the job, but in the meantime...

The answer to the immediate question of how to create more than 128 Fields (Columns) in a Table:
(1) From the Menu select Tools > SQL...
(2) See the SQL ALTER TABLE ADD COLUMN command along with the available Data Types.
(3) Press Execute
(4) Press Close
(5) Select View > Refresh Tables to see the results in Base

And there's probably a way to create two or more 128 Field Tables simply using the normal GUI Table Design View in Base, and then before adding data, combine them into one 'wide' Table with an SQL command or two. I'll leave that workaround to the SQL experts...

If nothing else, it would take mere seconds to modify the Table definitions file (.script file in HSQLDB) manually to combine any number of empty 128 Field/Column Tables into one 'wide' Table using a text editor. In fact, that's effectively how I reached 8192 columns in the testing mentioned above -- combining 64 * 128 column tables. Just be sure you have a backup of the database before venturing down this path.

[MalM]

Thanks I will give it a try and let you know if I run into trouble.

M.

[MalM]

Ok I tried it and it did not work. My table name is MCTD CRF and the field name is EDVI (mL/m2) and field type is number.

I never used SQL before, however this is how I entered it:
ALTER TABLE MCTD CRF
ADD EDVI mL/m² number

I keep getting a message under the status as "Table not found: MCTD in statement [ALTER TABLE MCTD]"
what do you suggest?

Thanks

M.

[r4zoli]

If you use table or field name with space in, you must use "double quotes", if not use you get error.
Good practice to use letters, numbers and underscore only in database object name.

[MalM]

Hi

Thanks I figured out my mistake. I did not add the word COLUMN to the command. I also took your advice and changed the name to Crf&MrData (without spaces)

I used:
ALTER TABLE "Crf&MrData"
ADD COLUMN "EDVI (mL/m2)" NUMERIC

That seem to have done the trick.

Thanks again r4zoli and DACM.

M.

[DACM]

I did not add the word COLUMN to the command.

I thought [COLUMN] was optional myself, but apparently not.
see: http://www.hsqldb.org/doc/guide/ch09.ht ... le-section