Question for any IT Pros out there:

[Parker2112]

Lets say my company has a pre-existing database that we are about to populate with info from a new project, and lets say the database was set up by another firm prior to my company's actually knowing what fields would be needed. So we have some fields we need and we now realize we are missing some that we will need for reporting to the client.

Now lets say that getting IT to program new cells/tables in the database is going to be more expensive than we might like.

And last, lets say you have some large/general "comment" fields provided in the database.

My question is this: Would it be possible for our office to enter several data strings into that "comments" field, delineated by a semi-colon maybe,(eg. "gross-50; tax-100; startdate-01/01/1990;"), and have IT set us up to run queries/reports from these entries?

If so, what would be the best way to do this maybe?

[dimsah]

It's certainly possible to create a large variable character column which is essentially free form and it can be delimited using any number of special characters.

These types of columns are very poor for queries however if there is no real structure to the column itself because there are few ways to index it which means the SQL engine of the database platform will perform full table scans on the table in question. If this is a large table, it can take a looooong time to complete. You could implement partitioning which could reduce the overall impact but you still have to partition based on an existing column of data.

If the tables in the database are relatively small then you could probably get away with using qualifying predicates on a large variable character field but it's still very inefficient.

[Parker2112]

It's certainly possible to create a large variable character column which is essentially free form and it can be delimited using any number of special characters.

These types of columns are very poor for queries however if there is no real structure to the column itself because there are few ways to index it which means the SQL engine of the database platform will perform full table scans on the table in question. If this is a large table, it can take a looooong time to complete. You could implement partitioning which could reduce the overall impact but you still have to partition based on an existing column of data.

If the tables in the database are relatively small then you could probably get away with using qualifying predicates on a large variable character field but it's still very inefficient.

So Dimsah, if we had, say 1600 records and in a particular column we had a string of 5 dates delineated by some special character, do you think this would be prohibitively cumbersome to work with?

[CubanMustGo]

1600 records isn't a database, it's an excel spreadsheet.

[dimsah]

So Dimsah, if we had, say 1600 records and in a particular column we had a string of 5 dates delineated by some special character, do you think this would be prohibitively cumbersome to work with?

1600? That wouldn't be a problem at all. Even large data columns would still amount to a small number of data blocks consumed on so few records so even full table scans would not be prohibitive.

I was assuming the tables had millions if not hundreds of millions of records.

[koriwhat]

1600 records isn't a database, it's an excel spreadsheet.

hahaha

[Viva Las Espuelas]

:lol

[lazerelmo]

Now lets say that getting IT to program new cells/tables in the database is going to be more expensive than we might like.



Tell your IT People to Google ALTER TABLE.

[RandomGuy]

1600 records isn't a database, it's an excel spreadsheet.

The new Excel will handle up to a million lines. I think you have a good point here.

[tlongII]

1600 records isn't a database, it's an excel spreadsheet.

Yep. I would dump that database out to excel and then do whatever I wanted with it.

[ChuckD]

Tell your IT People to Google ALTER TABLE.