I have tables with several columns that are yes/no switches. I tried using defining the columns as byte(1) and using values of 0 or 1, but there seem to be problems with this, and I'm not sure the byte type was designed for this use. What do you recommend? Also, should columns of this kind--that contain one of two values--be indexed, even if they're references in sql where clauses?
The byte datatype could be used for that, as could a smallint or integer. What sort of problems were you seeing?
As far as indexing you can add the field to a metamorph compound index if you are doing like queries in conjunction. Otherwise the more uneven the distribution of values the more useful an index might be.
OK, Table1 contains info about resources that are available in formatA or formatB, or both. So I have columns formatA and formatB. Over 95% of the items are formatA only; most of the rest are formatB only, and a few are both. I have a normal index on each column.
Table2 contains more detailed info about items available in formatB. It has ten columns representing types of content (one or more) which may apply to the item. I have a normal index on each of the ten columns.
As far as problems are concerned, there are vortex errors such as:
<!-- 100 import_dbofdbs_dbnotes:15: FTN type 1 (byte): cannot convert to SQL t
ype; assuming byte in the function vsvtx_setparam -->
Depending on what sort of queries you are doing you may want to just use one table. If you are doing single clause queries the indexes may be useful. The formatA index is unlikely to be useful otherwise unless you are querying for those with formatA=0.
The message seems to be an warning that should not have been thrown in that case. You could avoid it by writing a putmsg function, or by converting the variable to an int before passing it into another SQL.
I decided to use a char(1) column for Table1, with values for formatA, formatB, and both. This seems to work OK. However, I'd like to pursue the indexing issue a bit more. Generally, the where clause of the sql looks like:
" ... where (format = 'a' or format = 'c') and textfield [matches|like] $key"
I have normal and metamorph indexes on textfield. Currently there is no index on format.
As for Table2, I stayed with byte(1) as the data type for the ten flags. There are no indexes on the flag columns. As I mentioned in a previous message, this table contains more detailed info on items in formatB. I decided not to incorporate this information into Table1 because the formatB items comprise less than 5% of the total items, and because there are almost 30 columns in Table2, which would be all blank if I put them in Table1. So, there is a one-to-one relationship between Table1 and Table2, and I do have to join the two tables frequently in queries. Would it still be better to merge the info into one table? The performance is OK, although I'd like it to be a bit better.
