Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
Real fields vs computed fields vs ?? |
Mon, Jul 7 2008 3:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Jul 7 2008 1:35 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Jul 7 2008 4:14 PM | Permanent Link |
Heiko Knuettel | 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 |
Tue, Jul 8 2008 3:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Jul 8 2008 4:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |