I'm seeking people's opinions here.

In the email table for my app I was going to have several status fields (boolean and integer)  but, since they would only be addressed on a currently displayed record basis I decided to move to having a single VARCHAR(10 gives room for growth) with Y's and N's indicating a specific status condition. I'm now thinking of using it for a couple more: display status & encoding. These would be have a specific letter rather than Y or N.

If I do this I could then access then via SUBSTR in sql or .AsString[index] in Delphi

OR

I can create COMPUTED fields for them eg for one of the simpler ones

ALTER TABLE "EMails"
ADD COLUMN "_sContactExists" BOOLEAN COMPUTED ALWAYS AS IF(SUBSTR(_Status,1,1)='Y',TRUE,FALSE)

If I go down this route what's the opinion on naming conventions. My current one (developed since I use persistent fields wherever possible) is

1. Fieldnames are prefaced with _
2. Foreign keys are _fk plus tablename
3. Calculated fields are _x plus fieldname

I was thinking of _s plus fieldname (s for shadow)

All opinions welcome

Roy Lambert

Roy,

<< In the email table for my app I was going to have several status fields
(boolean and integer) but, since they would only be addressed on a currently
displayed record basis I decided to move to having a single VARCHAR(10 gives
room for growth) with Y's and N's indicating a specific status condition.
I'm now thinking of using it for a couple more: display status & encoding.
These would be have a specific letter rather than Y or N. >>

What is the reasoning behind not using individual columns ?  Personally, I
would stick with individual columns.  Otherwise, you're throwing away 80+%
of the convenience of data access via code or SQL.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

With DBISAM, I always used Integers for that. One integer Field = 31 boolean fields, a
function to read them, a function to write them, and a derived TDBCheckbox with an
additional flagno property. Filtering speed was the same as with boolean fields. They
saved disk space (big tables = slow tables, at least in DBISAM, I have no experience in
EDB with that). And I was able to add a "boolean field" anytime - without need to alter
the table. Nice thing, never had problems with it.

Now, with EDB I'm not sure about the performance...since there are no bitwise operators in
SQL, I have to use an external module for this two (my most used) functions, and I already
read about performance issues with external modules. I will have to check that out. If it
becomes too slow, I will convert them to Y/N-CHAR-fields. Would negate the table size
benefit, but as I said, I don't know if table size has the same impact on performance as
it had in DBISAM.

As for naming conventions...well, I don't use any ...I usually remember my field
names, and when I die someday, someone will have a hell of a job...but i don't think I
will care about that much, then

Heiko

Heiko

>As for naming conventions...well, I don't use any ...I usually remember my field
>names, and when I die someday, someone will have a hell of a job...but i don't think I
>will care about that much, then

ROFLOL

Roy Lambert

Tim


>What is the reasoning behind not using individual columns ? Personally, I
>would stick with individual columns. Otherwise, you're throwing away 80+%
>of the convenience of data access via code or SQL.

I do wish people wouldn't ask questions like that - it means I have to think which is always painful.

1. Creating a VARCHAR(10) essentially gives me up to 10 status flags

2. I can start with using 5 that I have identified a use for, but if I need the other 5 it needs no restructuring of the table

3. Initially 3 will be boolean (ie Y or N), but if I want I can alter them to (up to) 36 states again without restructuring the table

4. Checking the code for the app being converted of the 5 all are set when the email arrives/is decoded and one (reply sent) MAY be altered when the email is read. None of the 5 I've currently identified have never been used in a query or filter although one is used as the controlling column for text indexing

5. It feels a "neater" solution than creating 5 (or 10) fields which are essentially just going to sit there doing nothing most of the time

I suppose the reason for the post was to see what other peoples views were / to validate the approach.

Roy Lambert