I am a refugee from 4D. So my database experience is a little bit warped. I would love some input as to the best way to do something.
Let’s say I have three fields:
First name (Geoff)
Last name (Perlman)
Company name (Xojo Inc)
I wish to display those fields regularly in a list box. But of course I don’t want separate columns for these three fields. I would rather join them together into a single field called simply: NAME
If there is a company name, NAME would equal Company name (Xojo Inc)
If there is not a company name, NAME would equal last name + comma+ first name (Perlman, Geoff)
Of course in the list box I would want to sort alphabetically what it is displayed.
Imagine a database with A few million records and sucking the raw data of the three fields into XOJO and then sorting it out before I send it to the list box: well that’s too big of a pain in the butt.
So my thoughts are this:
Should I even be fooling with the creation of the NAME in Xojo at all?
Should I be doing it in mySQL?
Should I store it as a calculated field in mySQL? Perhaps even as some sort of procedure and mySQL? I could write a very complex query to calculate it on the fly in mySQL but that seems very inefficient also.
I would love any input from anyone on this matter.
You can do this in a “calculated field” (actually, a trigger that you’d set up to work on insert or update), or you can do this in the SQLSelect statement, something like this:
SELECT
IF (
TRIM( company ) = '',
CONCAT_WS(', ', TRIM( lastname ), TRIM( firstname ) ),
TRIM( company )
) AS keyfield
FROM the_table
This would return a field “keyfield” with the data you seek.
The equivalent Xojo code:
dim keyfield as string
if company.Trim = "" then
keyfield = Join( Array( last name.Trim, firstname.Trim ), ", " )
else
keyfield = company.Trim
end if
If you’ve already trimmed the fields, the additional .Trim is not needed.
This code would return the company if it’s there, or either of the names if only one is present, or both separated by a comma if both are present.
Xojo, Inc.
Geoff // last name is empty
Perlman // first name is empty
Perlman, Geoff
If you need to manipulate large volumes of data do it in the database server, a reasonably well tuned database server is hard to beat in application code in most cases.
One of the benefits of the third-party database environments like 4D and FileMaker Pro is that it handles stuff like this for you. Define a field as a “calculation” in FileMaker, for example, and it will properly recalculate if one of the fields its based on get changed. No separate code for creating a trigger or anything like that.
No
Thats usually only when you initially create the row
I tend to avoid putting computed values in the DB anyways - unless I’ve deliberately renormalized a table (think accounting and quarterly/annual values where you REALLY don’t want to try & sum them up on the fly if the DB gets to be reasonably large)
In most places a computed value is a presentation issue (FULL NAME vs first & last) and NOT something you worry about in a normalized database
I have no problem with views & stored procedures.
I don’t like putting business logic in stored procs though.
The first time you end up moving your DB to another one because the CIO signed some awesome deal with another vendor you want to kill the CIO and then yourself trying to rewrite all those procedures.
Been there did that from Sybase to Oracle & all the business logic ended up in a middle tier after that so we could swap whatever back ends we wanted without repercussions.
Or someone wants your software but hosted on some other DB and its procedure language doesn’t support what you need.
[quote=59369:@Norman Palardy]I have no problem with views & stored procedures.
I don’t like putting business logic in stored procs though.
The first time you end up moving your DB to another one because the CIO signed some awesome deal with another vendor you want to kill the CIO and then yourself trying to rewrite all those procedures.
Been there did that from Sybase to Oracle & all the business logic ended up in a middle tier after that so we could swap whatever back ends we wanted without repercussions.
Or someone wants your software but hosted on some other DB and its procedure language doesn’t support what you need.
[/quote]
I think the answer to this is… depends.
In some cases, having logic built into SP is a must while other times its not necessary. It all depends on the application functionality and if the database is an extension of that functionality.
