Re: Storing a great many fields in a database
I don't know if you are referring to SQLite or other SQL implementations, but there is no varchar in sqlite. You can define a column as varchar but sqlite will give it a type of TEXT internally. SQLite does have some advantages over other dbs in this area. Since it effectively ignores any length specification, e.g. VARCHAR(100), it only stores the number of characters you give it during an INSERT or UPDATE operation. On the other hand VARCHAR(100) on most other SQL dbs will allocate enough disk space to store 100 characters even if you only put 1 character in the column. The 100 is a constraint that prevents more than 100 chars from being put into the column but doesn't affect the storage space. If you're only ever going to access this db with LC, I'd probably use true and false as your boolean values. If you plan on using other tools to access it, go with 0 and 1. As for integer arithmetic being slow, I doubt you'd notice any difference whatsoever unless you expect to process hundreds of thousands of rows in one operation. As you noted, using REAL for currency arithmetic will get you into rounding issues but sounds like that's not a problem. One approach I've seen for currency values is to store them as integers including the decimal places, e.g $100.00 would be stored as 1, do the arithemtic in that form, then insert the decimal point for display purposes. Pete lcSQL Software http://www.lcsql.com On Mon, Jul 16, 2012 at 8:31 AM, Dr. Hawkins doch...@gmail.com wrote: OK, blobs are overkill, and then some. It looks like varchar() is the way to go. It looks like a main table for the financials, with the booleans and integers stuffed there as well and then a varchar table for the strings. (storing true as 1 and false as 0?) I note documentation that refers to numeric/decimal as very slow as compared to floats (i'd need doubles, anyway). Just how much is very slow; is it even relevant on a remote database? An occasional penny rounding error isn't really critical for this type of work. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
Bob, SQLite will always use TEXT and allocate just as much space as necessary to store the information at hand. I think it is a better option. So if you just store 10 chars in that column, the TEXT field will use just that space and nothing more. On Mon, Jul 16, 2012 at 2:58 PM, Bob Sneidar b...@twft.com wrote: Wha?? I thought the whole point to using VARCHAR was that it only used as much space as the actual text needed. What is the difference between TEXT and VARCHAR then? Bob On Jul 16, 2012, at 9:50 AM, Peter Haworth wrote: SQLite does have some advantages over other dbs in this area. Since it effectively ignores any length specification, e.g. VARCHAR(100), it only stores the number of characters you give it during an INSERT or UPDATE operation. On the other hand VARCHAR(100) on most other SQL dbs will allocate enough disk space to store 100 characters even if you only put 1 character in the column. The 100 is a constraint that prevents more than 100 chars from being put into the column but doesn't affect the storage space. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode -- http://www.andregarzia.com -- All We Do Is Code. http://fon.nu -- minimalist url shortening service. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
I think then that for SQLITE TEXT is actually synonymous with VARCHAR. It was my understanding that VARCHAR allowed for an economical use of space, but was slower to index. The engine would use just enough space, up to the limit set, to store the values, truncating the excess. I seem to remember that one of the string types (perhaps CHAR) always stored trailing spaces up to the limit, and this produced faster indexes. I could be wrong though as I am not terribly experienced in database stuff, being only a hobbyist. Bob On Jul 16, 2012, at 11:08 AM, Andre Garzia wrote: Bob, SQLite will always use TEXT and allocate just as much space as necessary to store the information at hand. I think it is a better option. So if you just store 10 chars in that column, the TEXT field will use just that space and nothing more. On Mon, Jul 16, 2012 at 2:58 PM, Bob Sneidar b...@twft.com wrote: Wha?? I thought the whole point to using VARCHAR was that it only used as much space as the actual text needed. What is the difference between TEXT and VARCHAR then? Bob ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
On Mon, Jul 16, 2012 at 11:26 AM, Bob Sneidar b...@twft.com wrote: I think then that for SQLITE TEXT is actually synonymous with VARCHAR. It was my understanding that VARCHAR allowed for an economical use of space, but was slower to index. The engine would use just enough space, up to the limit set, to store the values, truncating the excess. I seem to remember that one of the string types (perhaps CHAR) always stored trailing spaces up to the limit, and this produced faster indexes. according to postrgesql's docs, it costs the string length plus one byte for varchar of less than 126 characters, and the stringlength + 4 bytes for anything longer. -- The Hawkins Law Firm Richard E. Hawkins, Esq. (702) 508-8462 hawkinslawf...@gmail.com 3025 S. Maryland Parkway Suite A Las Vegas, NV 89109 ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
On 7/16/12 9:08 PM, Andre Garzia an...@andregarzia.com wrote: Hi guys, Let me explain a little LOW LEVEL ideas for VarChar and TEXT ? 1) So Peter says that SQLite always ignore length spec and store only given chars, and Peter think that MOST OTHER dbs will eat 100 chars even if present 1. Peter this is wrong information (about OTHER dbs) !! Read below. 2) MOST databases, for example mySQL, Postgre and our Valentina DB have a) STRING(N) -- fixed length string type. This one always eat 100 chars as you say b) VarChar(N) -- variable length string ... This one eat given M chars + few helper bytes c) TEXT -- this differ from VarChar in the way how string is stored. and TEXT field is unlimited You can ask self Why exists VarChar and TEXT ??? Short answer is: * row-based dbs as mySQL and postgre keep the whole record as single buffer. * row-based dbs use PAGES to store a table. * even column-based db as Valentina use pages for VarChar column. So they get obvious LIMIT on size of total record, usually at least 2 records should fit one page ... It is know that e.g. MS SQL have 1300 bytes for total size of a record. TEXT field, allow break this limit, because data stored outside of record ... Bob, SQLite will always use TEXT and allocate just as much space as necessary to store the information at hand. I think it is a better option. So if you just store 10 chars in that column, the TEXT field will use just that space and nothing more. On Mon, Jul 16, 2012 at 2:58 PM, Bob Sneidar b...@twft.com wrote: Wha?? I thought the whole point to using VARCHAR was that it only used as much space as the actual text needed. What is the difference between TEXT and VARCHAR then? Bob On Jul 16, 2012, at 9:50 AM, Peter Haworth wrote: SQLite does have some advantages over other dbs in this area. Since it effectively ignores any length specification, e.g. VARCHAR(100), it only stores the number of characters you give it during an INSERT or UPDATE operation. On the other hand VARCHAR(100) on most other SQL dbs will allocate enough disk space to store 100 characters even if you only put 1 character in the column. The 100 is a constraint that prevents more than 100 chars from being put into the column but doesn't affect the storage space. -- Best regards, Ruslan Zasukhin VP Engineering and New Technology Paradigma Software, Inc Valentina - Joining Worlds of Information http://www.paradigmasoft.com [I feel the need: the need for speed] ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
On Mon, Jul 16, 2012 at 9:50 AM, Peter Haworth p...@lcsql.com wrote: I don't know if you are referring to SQLite or other SQL implementations, but there is no varchar in sqlite. You can define a column as varchar but sqlite will give it a type of TEXT internally. I'm using sqlite at the moment, but need to move to mysql or postgresql to put htis on servers. If you're only ever going to access this db with LC, I'd probably use true and false as your boolean values. If you plan on using other tools to access it, go with 0 and 1. It's hard to put true into a numeric value :) (thus the coding) As for integer arithmetic being slow, I doubt you'd notice any difference whatsoever unless you expect to process hundreds of thousands of rows in one operation. It wasn't integer being slow, but rather the numeric/decimal type that is very slower than float/integer for database access (it's coded as two bcd digits/byte, and needs to be undone) As you noted, using REAL for currency arithmetic will get you into rounding issues but sounds like that's not a problem. One approach I've seen for currency values is to store them as integers including the decimal places, e.g $100.00 would be stored as 1, do the arithemtic in that form, then insert the decimal point for display purposes. Actually working with the pennies, desirable as it would be, creates new issues--displaying in livecode fields. I think it will be simpler to store in pennies, and convert to dollars while working, and back on save. -- The Hawkins Law Firm Richard E. Hawkins, Esq. (702) 508-8462 hawkinslawf...@gmail.com 3025 S. Maryland Parkway Suite A Las Vegas, NV 89109 ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
The engine (if you mean the Livecode engine) has nothing to do with it, or at least it shouldn't. How VARCHAR works varies between SQL implementations. For sqlite, there is no difference between TEXT and VARCHAR - they end up using only as much storage as needed. Pretty much the same for mySQL, but I believe other implementations don;t work that way. Pete lcSQL Software http://www.lcsql.com On Mon, Jul 16, 2012 at 11:26 AM, Bob Sneidar b...@twft.com wrote: I think then that for SQLITE TEXT is actually synonymous with VARCHAR. It was my understanding that VARCHAR allowed for an economical use of space, but was slower to index. The engine would use just enough space, up to the limit set, to store the values, truncating the excess. I seem to remember that one of the string types (perhaps CHAR) always stored trailing spaces up to the limit, and this produced faster indexes. I could be wrong though as I am not terribly experienced in database stuff, being only a hobbyist. Bob On Jul 16, 2012, at 11:08 AM, Andre Garzia wrote: Bob, SQLite will always use TEXT and allocate just as much space as necessary to store the information at hand. I think it is a better option. So if you just store 10 chars in that column, the TEXT field will use just that space and nothing more. On Mon, Jul 16, 2012 at 2:58 PM, Bob Sneidar b...@twft.com wrote: Wha?? I thought the whole point to using VARCHAR was that it only used as much space as the actual text needed. What is the difference between TEXT and VARCHAR then? Bob ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
On Mon, Jul 16, 2012 at 12:07 PM, Dr. Hawkins doch...@gmail.com wrote: If you're only ever going to access this db with LC, I'd probably use true and false as your boolean values. If you plan on using other tools to access it, go with 0 and 1. It's hard to put true into a numeric value :) (thus the coding) Once again, sql terminology problem. BOOLEAN in sqlite translates to TEXT, not a numeric value. But now you've said you'll be using mySQL or postgres, the choice is yours. As for integer arithmetic being slow, I doubt you'd notice any difference whatsoever unless you expect to process hundreds of thousands of rows in one operation. It wasn't integer being slow, but rather the numeric/decimal type that is very slower than float/integer for database access (it's coded as two bcd digits/byte, and needs to be undone) Sounds reasonable. But again, unless you are processing large numbers of ros, it's not worth worrying about. Ther can't be THAT many people going bankrupt in NV can there :-) As you noted, using REAL for currency arithmetic will get you into rounding issues but sounds like that's not a problem. One approach I've seen for currency values is to store them as integers including the decimal places, e.g $100.00 would be stored as 1, do the arithemtic in that form, then insert the decimal point for display purposes. Actually working with the pennies, desirable as it would be, creates new issues--displaying in livecode fields. I think it will be simpler to store in pennies, and convert to dollars while working, and back on save. Uhhh, I think that's what I said, or maybe not…. In any case, formatting an integer to display with a decimal point in LC is trivial. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
I did not mean the LC engine I meant the SQL engine (if it is acceptable to refer to it in that way). Bob On Jul 16, 2012, at 12:45 PM, Peter Haworth wrote: The engine (if you mean the Livecode engine) has nothing to do with it, or at least it shouldn't. How VARCHAR works varies between SQL implementations. For sqlite, there is no difference between TEXT and VARCHAR - they end up using only as much storage as needed. Pretty much the same for mySQL, but I believe other implementations don;t work that way. Pete ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
On Jul 16, 2012, at 3:07 PM, Dr. Hawkins wrote: As you noted, using REAL for currency arithmetic will get you into rounding issues but sounds like that's not a problem. One approach I've seen for currency values is to store them as integers including the decimal places, e.g $100.00 would be stored as 1, do the arithemtic in that form, then insert the decimal point for display purposes. Actually working with the pennies, desirable as it would be, creates new issues--displaying in livecode fields. I think it will be simpler to store in pennies, and convert to dollars while working, and back on save. For his entire life, my father kept the running balances in his checkbook register in whole dollar amounts only, rounding each time he re-totaled. At the end of the year he was off by at most $2, usually accurate to the nearest dollar. In certain contexts, ignoring the small change doesn't matter at all for the big picture. -- Peter Peter M. Brigham pmb...@gmail.com http://home.comcast.net/~pmbrig ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
So you are saying we should just get rid of pennies? Actually that story is at the same time shocking, and yet makes perfect sense. It would be a great experiment to do that today and see what happens, especially when people charge n.99 for things. Bob On Jul 16, 2012, at 3:36 PM, Peter M. Brigham wrote: On Jul 16, 2012, at 3:07 PM, Dr. Hawkins wrote: As you noted, using REAL for currency arithmetic will get you into rounding issues but sounds like that's not a problem. One approach I've seen for currency values is to store them as integers including the decimal places, e.g $100.00 would be stored as 1, do the arithemtic in that form, then insert the decimal point for display purposes. Actually working with the pennies, desirable as it would be, creates new issues--displaying in livecode fields. I think it will be simpler to store in pennies, and convert to dollars while working, and back on save. For his entire life, my father kept the running balances in his checkbook register in whole dollar amounts only, rounding each time he re-totaled. At the end of the year he was off by at most $2, usually accurate to the nearest dollar. In certain contexts, ignoring the small change doesn't matter at all for the big picture. -- Peter Peter M. Brigham pmb...@gmail.com http://home.comcast.net/~pmbrig ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
On Saturday, July 14, 2012, Mark Wieder wrote: Saturday, July 14, 2012, 3:16:25 PM, you wrote: Or split it into two tables, and let my get/set functions figure out which to use, one for currency values, and the other for everything else? OMG. You have that many fields in *one* table? I think you need a serious database redesign. Or design, since it sounds like it was never really designed in the first place. Come up with a database schema, split the data into component tables, and this will all become much easier. Name address, phone,,last 4 of SS, prior filing, total wage income for six prior months, total business income 6 mo, bus expenses 6 mo, bus net 6 mo, real estate gross 6 months. . . . . Employer, Employer address, monthly health insurance from wages, monthly health insurance no wage, total health ins. Monthly, ... Total insurance monthly . . . It's really a situation of 400-500 variables to report, some of which are dependent upon and calculated by others (and once calculated, need to be kept available for further calculation, and for which the calculated value may be overridden). Unfortunately, there is no general pattern, although for a significant portion, they are repeated for the codebtor (wife) in a joint filing. Currently, with SQLite, I que there are that many single use data (outside of the assets and debts) in a bankruptcy petition. -- The Hawkins Law Firm Richard E. Hawkins, Esq. (702) 508-8462 hawkinslawf...@gmail.com 3025 S. Maryland Parkway Suite A Las Vegas, NV 89109 ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
Ack, iPad pre launched message . . . On Sunday, July 15, 2012, Dr. Hawkins wrote: On Saturday, July 14, 2012, Mark Wieder wrote: Saturday, July 14, 2012, 3:16:25 PM, you wrote: Or split it into two tables, and let my get/set functions figure out which to use, one for currency values, and the other for everything else? OMG. You have that many fields in *one* table? I think you need a serious database redesign. Or design, since it sounds like it was never really designed in the first place. Come up with a database schema, split the data into component tables, and this will all become much easier. It's really a situation of 400-500 variables to report, some of which are dependent upon and calculated by others (and once calculated, need to be kept available for further calculation, and for which the calculated value may be overridden). Name address, phone,,last 4 of SS, prior filing, total wage income for six prior months, total business income 6 mo, bus expenses 6 mo, bus net 6 mo, real estate gross 6 months. . . . . Employer, Employer address, monthly health insurance from wages, monthly health insurance no wage, total health ins. Monthly, ... Total insurance monthly . . . there are that many single use data (outside of the assets and debts) in a bankruptcy petition. Unfortunately, there is no general pattern, although for a significant portion, they are repeated for the codebtor (wife) in a joint filing. a strong majority are currency values, but the strings I need vary from 1 char to about 200 (for an address) with SQLite, I have a row of keyword, value, override, and default. I've recently added a fifth column with values used to calculate the fpdefault in certain cases, but I'd like to dump it. (well, store separately). this is going to quickly clobber space, though, if every number and Boolean needs 3 200 char fields in storage (roughly a quarter megabyte for the resultant file) I'm toying with a number table and a string table; it's straightforward enough for he put/get routines to use the custom property on the fields to direct traffic. Integers (always small), generally low single digits) could be store there trivially; that's just a display of the decimal issue. Booleans could be saved any number of ways (are non-zero vales true for an if?). That still leaves me with the ugly strings question, though. the actual value is actually redundant; if there is an override, it's the valu. If not, the default, if any, is used. Most of the strings don't have defaults, anyway; i could store the default s as separate rows. I could also put the extra blocks for some of the defaults mentioned above in here. it how to control the size of this table? Or do I simply accept that I allocate 20 chars even for one byte strings? -- The Hawkins Law Firm Richard E. Hawkins, Esq. (702) 508-8462 hawkinslawf...@gmail.com 3025 S. Maryland Parkway Suite A Las Vegas, NV 89109 ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
On Saturday, July 14, 2012, Peter Haworth wrote: Are you saying that you want to store the default, override, and actual values in the table row of the database? If so, that deosn't feel right to me. I'd split that into a default table, an override table, and actual value table, each connected by a foreign key to the primary key of the table that idntifies the debtor, (name/address, SSN, etc). I'm actually outing each debtor in a separate database. There is really no cross-over from case to case, and one getting corrupted (especially by a coding error!) doesn't kill the other client files . I'm also not comfortable from mixing data for different attorneys in the same database. Also, as far as performance, there will be thousands (tens of thousands?) of debtors on the server, each with a few hundred records. won't performance be significantly better if they're in their own databases? if I'm generally working with the triplet of fields as a set, what would be the value of putting them in separate tables? I always need to know if they exist, and I can pull all three in a single query. Another approach might be to try to group the fields into categories that logically belomng together somehow and have a separate table for each category, once again linked by foreign key to the m ain debtor table. Don't know if that's possible. Logic and US bankruptcy law face little in common :) More seriously, to the extent that they group with any rhyme or reason, it's currency values with the occasional Boolean, integer, and string. THe more I think about it, the more I think it will be an issue of storing strings in their own table;the others can transparently cohabit in a currency table. If you're saying that datatypes vary by row in the same table, that's a definite no no in any database design, more so in anything other than SQLite since they all have strict typing rules and you will get errors returned if you try to insert a column with a datatype that is different than defined in the schema. You'll need to design your tables so that any given column in a table will always have the same datatype. If I understand your email correctly, sounds like there should at least be separate asset and debt table they're in a separate table, yes. It gets multiple rows, one for each asset/debt. Thanks -- The Hawkins Law Firm Richard E. Hawkins, Esq. (702) 508-8462 hawkinslawf...@gmail.com 3025 S. Maryland Parkway Suite A Las Vegas, NV 89109 ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Storing a great many fields in a database
Currently I'm using the included SQLite while I'm figuring the whole program out, but to stay ahead of the competition, I need to be able to store in the cloud (particularly, for attorneys to use virtual assistants in other parts of the countries, and to access files by iPad in court). Anyway, SQLite cheerfully ignores the data type entirely. I assume that I'll stay compatible with both mySQL and postgreSQL, but that's where the issue is. I have a great many fields that provide the description of the debtor (it's a bankruptcy program). At the moment, there are 276 of them (and will probably be about 400 when complete). Most of these are money values (decimal(12,2)). A handful are boolean, and the rest text ranging from 1 to 200 characters. I need to be able to access them by name, so currently there is a keyword for each, and three different values for each one(a default, an override, and the actual value). I've been happily assigning data types in a custom property field, and even fixed them up today. And then it occurred to me that I don't get to specify a different data type by row . . . (I have debt and asset information with a great many per debt, so that goes in a different table). Given that the norm will probably be a remote rather than local database server, what is the best way for me to structure the table? My 200 character upper limit seems to suggest that blobs would be overkill, but allocating three 200 character strings per entry seems like serious overkill--or is it? I could have 400 columns, I suppose, with three rows (for each of those values)--but isn't this going to slow down the server? Or split it into two tables, and let my get/set functions figure out which to use, one for currency values, and the other for everything else? Or will the minimum row size mean that at 200 characters each, it just doesn't matter anyway? -- The Hawkins Law Firm Richard E. Hawkins, Esq. (702) 508-8462 hawkinslawf...@gmail.com 3025 S. Maryland Parkway Suite A Las Vegas, NV 89109 ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
Doc- Saturday, July 14, 2012, 3:16:25 PM, you wrote: Or split it into two tables, and let my get/set functions figure out which to use, one for currency values, and the other for everything else? OMG. You have that many fields in *one* table? I think you need a serious database redesign. Or design, since it sounds like it was never really designed in the first place. Come up with a database schema, split the data into component tables, and this will all become much easier. -- -Mark Wieder mwie...@ahsoftware.net ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Storing a great many fields in a database
It's a little hard to decipher exactly waht your're trying to achieve here but here's a few thoughts.. Are you saying that you want to store the default, override, and actual values in the table row of the database? If so, that deosn't feel right to me. I'd split that into a default table, an override table, and actual value table, each connected by a foreign key to the primary key of the table that idntifies the debtor, (name/address, SSN, etc). Another approach might be to try to group the fields into categories that logically belomng together somehow and have a separate table for each category, once again linked by foreign key to the m ain debtor table. Don't know if that's possible. If you're saying that datatypes vary by row in the same table, that's a definite no no in any database design, more so in anything other than SQLite since they all have strict typing rules and you will get errors returned if you try to insert a column with a datatype that is different than defined in the schema. You'll need to design your tables so that any given column in a table will always have the same datatype. If I understand your email correctly, sounds like there should at least be separate asset and debt tables. Pete lcSQL Software http://www.lcsql.com On Sat, Jul 14, 2012 at 3:16 PM, Dr. Hawkins doch...@gmail.com wrote: Currently I'm using the included SQLite while I'm figuring the whole program out, but to stay ahead of the competition, I need to be able to store in the cloud (particularly, for attorneys to use virtual assistants in other parts of the countries, and to access files by iPad in court). Anyway, SQLite cheerfully ignores the data type entirely. I assume that I'll stay compatible with both mySQL and postgreSQL, but that's where the issue is. I have a great many fields that provide the description of the debtor (it's a bankruptcy program). At the moment, there are 276 of them (and will probably be about 400 when complete). Most of these are money values (decimal(12,2)). A handful are boolean, and the rest text ranging from 1 to 200 characters. I need to be able to access them by name, so currently there is a keyword for each, and three different values for each one(a default, an override, and the actual value). I've been happily assigning data types in a custom property field, and even fixed them up today. And then it occurred to me that I don't get to specify a different data type by row . . . (I have debt and asset information with a great many per debt, so that goes in a different table). Given that the norm will probably be a remote rather than local database server, what is the best way for me to structure the table? My 200 character upper limit seems to suggest that blobs would be overkill, but allocating three 200 character strings per entry seems like serious overkill--or is it? I could have 400 columns, I suppose, with three rows (for each of those values)--but isn't this going to slow down the server? Or split it into two tables, and let my get/set functions figure out which to use, one for currency values, and the other for everything else? Or will the minimum row size mean that at 200 characters each, it just doesn't matter anyway? -- The Hawkins Law Firm Richard E. Hawkins, Esq. (702) 508-8462 hawkinslawf...@gmail.com 3025 S. Maryland Parkway Suite A Las Vegas, NV 89109 ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode