Data Type

SQL Server Tables have Columns. Columns have data types. The data type of a column is stored in the metadata table Column table in md_column.mdc_data_type. Depending on the data type, certain netFORUM columns will have a suggested Table/Column Naming Conventions. Column Data Types are also closely associated with the column Control Class.

User-defined Data Types

netFORUM contains special User-defined Data Types (UDT) used for various purposes explained below. User-defined data types enable you to create specialized data types for a particular database. Generally, you should always use the netFORUM user-defined data types instead of base types. The only exception is for ordinary nvarchar columns. Use nvarchar and not varchar in order to support Unicode international characters.

In very rare and specialized cases, you might also use the char or nchar data types but this is uncommon, such as if you have a column that always holds a variable 2-character code, for example. Any free entry columns should be nvarchar.

Data Type List

netFORUM uses certain User-Defined-Data types including the following below. The "Desc" column is the value that appears in the data type dropdownlist on the Column page.

User-Defined DataType Base Data Type Desc Common Usage
none nvarchar String Used to store basic text such as First Name, Event Code, etc. Use Nvarchar for Unicode (aka international characters) support and NOT varchar. Do not use char or nchar except in very rare cases when all data will have the exact same width; using the char or nchar data type will put extra spaces on the right, up to the column width. Generally, if the column data type is not nvarchar, then it will be one of the user-defined data types that follow.
av_currency money Currency Used to store monetary currency.
av_date DateTime Date Time Date columns - January 1, 1753, through December 31, 9999. Will initialize column control class to DateTextBox and column input mask to '99/99/9999' to validate user's typed entry. Due to this SQL Server limitation we have no way to store dates before 1753 (this has come up for the Date Founded field!). If you really need to store pre-1753 dates then you'll be forced to use a different data type. Do not waste storage space with this data type if you are just recording ordinary "real world" dates--use av_date_small instead. Use this data type only when you might need to store a date that is far into the past or future.

By default, all columns with a data type of av_date, av_date_small, datetime, smalldatetime, have a column input mask of "99/99/9999". The input mask is what causes the time portion to be dropped when you call Avectra.netForum.Data.FacadeClass.SetValue. If you blank out (empty space) the input mask, then the full date and time will be saved. Do not make it null because the md_column_populate SP will switch it back to "99/99/9999". Also, if this control is ever placed on forms as an ordinary DateTextBox Control Class, the time portion will get clipped. If you really need to track dates down to the hour/minute/second, it is not easy to do so with a DateTime.

av_datetime DateTime Date with Time portion Used only when you need the Date and Time portion. If you need only the Date, and not the Time, then use av_date_small or av_date (for dates that can go far into the past and future).
av_date_small SmallDateTime Date Same as av_date, but dates that don't go too far back or forward - January 1, 1900, through June 6, 2079. Don't choose this if you need to store dates way out in the future. See also av_date above.
av_decimal2 decimal (19, 2) Decimal2 Used to store values with up to 2 digits to the right of the decimal point, eg 8970.32 or just 30.00.
av_decimal4 decimal (19, 4) Decimal4 Used to store values with up to 4 digits to the right of the decimal point, eg 76.0987 or just 89.0000.
av_delete_flag tinyint Delete Flag Specialized data type used only for the hide/disable checkbox column in every table. Do not use on an ad hoc basis.
av_email nvarchar(80) E-Mail Used for columns that store email addresses. See EmailTextBox. Avoid creating columns that store email addresses -- try instead to work with the baseline email table co_email. If you feel like you need to store an email address, see instead if you can have a FK column to store a pointer to co_email.eml_key.
av_fax nvarchar(30) Fax Used for columns that store fax numbers. Can be used to implement fax number masking
av_flag tinyint Flag Used for checkboxes. Make column be NOT NULL DEFAULT 0. Or "1" when checkbox should default to checked.
av_float float Float Used for numeric columns that can have more than 4 places to right of decimal point. Avoid using this as it takes more space. Try to use av_decimal2 or av_decimal4 unless you really need the precision of a float data type. This data type is rarely used; as of 2006.02, only six baseline columns use this data type, for example co_address.adr_latitude and co_address.adr_longitude.
av_integer int Integer Used for numeric columns (positive, negative or zero) with no decimals. If you want only 0 or positive numbers, or want to cap the number, then add a table check constraint. If you want to store a year, and you don't need full MMDDYYYY, then use av_integer and set a check constraint to enforce the entry of a plausible year (eg, x01_year >= 1900 AND x01_year <= 2100)
av_key uniqueidentifier GUID Primary key of table, and used by Foreign Key columns that store the value of a Primary Key from a different table (or a different column in the same table, pointing to a different row - example co_customer.cst_parent_cst_key). See GUID.
av_mailing_label nvarchar(300) n/a Specialized data type used by baseline netFORUM to generate mailing labels; done in programming code, should not be used on ad hoc basis as there is framework programming behind this.
av_messaging_name nvarchar(30) n/a n/a
av_phone nvarchar(30) Phone Similar to av_fax
av_picture image n/a Not implemented
av_recno bigint Record Number Used for identity columns. You will still need to set the identity property when designing the column.
av_text ntext Text

Important! Use nvarchar(max) instead per Microsoft deprecated notice on ntext. The av_text data type is based on the ntext base data type.
av_url nvarchar(200) URL Used for to enforce URL syntax formatting. See UrlTextBox
av_url_long nvarchar(400) URL Long Same as av_url but longer
av_user nvarchar(64) User Used by framework to store User names such as in the Add User and Change User columns. Use to store user names. No "magic" to this data type, you will still need to implement the front end for this (like convert it into a DropDownList if you want users to choose a value). Recommend against setting foreign key references in case the underlying fw_user database row is archived in the future. Baseline netFORUM tables to NOT add FK references in add or change user columns.

Custom User-defined Data Types

netFORUM does not provide for the ability to create custom client UDTs. Technically, you could create one and use it, but netFORUM will not "know" what to do with it. In the column definition, you must map your column to one of the netFORUM UDTs. Obviously, SQL Server will still honor or enforce the UDT definition, but the front end application will not know what to do with it, and your results can vary.

Additional Information