| Author |
Topic |
|
chacha
Starting Member
39 Posts |
 Posted - 2004-07-12 : 20:37:49
|
| I have a number of tables which serve as the equivalent of an enum in mysql. I would like to find a better way of representing a set of constant values. It seems a table is inapropriate and this data deserves it's own structure, like an enum.An example of what I have is...Cases-----pk_CaseIDFilingStatus varchar(50) not null default 'Unfiled' references FilingStati(FilingStatus)FilingStati-----------FilingStatus varchar(50) not null primary keyAm I missing the fact that there are enum types in SqlServer? Could UDT's be used to solve this problem? I have never used them. |
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-12 : 21:11:48
|
| Ok, so I am using Check Constraints instead. this is much better. Only one question - what would be a good general rule to follow about using check constraints vs. tables of constant values? A few issues I am considering surrounding this are...1. Will the values change frequently (if so, check constraints can be updated and applied to only the newly inserted column values. This would be much more elegant than adding an 'NoLongerSupported' column to the constants table signifying that that row value is no longer a valid foreign key)2. Do controls in consuming applications rely on the constant values as metadata (ie, dynamically generated radio buttons or dropdown lists). If so, the table is a much better solution.3. Do the constant values have associated descriptions or other metadata? If so, then the table is more appropriate because the constant and descriptor can be together (ie. InsuranceType / DescriptionOfInsuranceType)4. How many values are in the set? If there are enough to become confusing, then consider a table rather than a long "ColumnName IN ('blah', 'blue', 'red', 'foo', 'fee', 'fi', 'fo'..." |
 |
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-12 : 21:34:15
|
| Mmmmm. I might have jumped the gun here a little. This just doesn't smell quite right. Intuitively Check Constraints seem better suited to ensure that data falls within a range of values, or sanity checks. Like "dob < GET_DATE()" or "NumberChildren < 20" or "State = CA" or "AgeBeganReceivingBenefits BETWEEN AgeEligibleForBenefits AND ForcedRetirementAge". Is this correct? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-07-12 : 21:46:54
|
| Without sounding to harsh...You are missing a lot more than you realise..Anybody who has taken the time to understand the basics of the relational model would be crying right now..A type is a set of values and is the fundamental item in a relation.. An enum on the other hand is set of numeric values with a representation.So... a column in a table consists of a type and a name. When we add a row to the table the values MUST be of the same type and equal one of the values in the types value set. This is called a type constraint or domain constraint. Thus an enum is simply a very limited type..A CHECK constraint is the only way to enforce domain constraints in SQL.DavidM"Always pre-heat the oven" |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-12 : 23:04:58
|
| Thanks for the non-help byrmol. I understand that Sql Server doesn't actually have a datatype called Enum that one can assign to a column's datatype descriptor. I am asking for a reasonable alternative to representing sets of constant values that represent a domain of valid values for a particular column of data. This would be accomplished with the Enum type in mysql.I don't think that check constraints are ALWAYS appropriate for this purpose, although I raised the issue after google searching on the subject and finding that a number of commercial software providers use this approach when porting sql from Oracle to SqlServer, or other vendor's platforms that don't have inherent support for the Enum type. I have also learned that it is also common for vendors to use an ENUMS table that looks like...ENUMS-----EnumName varchar, -- the 'type' nameEnum int, -- the integer valueValue varchar -- the token identifier representing the underlying intThis table then holds values such as...('FilingStatus', 1, 'Unfiled')('FilingStatus', 2, 'Filed')('FilingStatus', 3, 'In Progress')('ConnectionType', 1, 'SqlServer')('ConnectionType', 2, 'Oracle')('ConnectionType', 3, 'Access')Another table, which uses the Enum 'type' (I know it's not an actual type in this case, thanks) looks something like...tblCases--------pk_CaseId int not null primary key,FilingStatus int not null default 1with a constraint ( FilingStatus IN (select Enum from ENUMS where EnumName = 'FilingStatus') )Or something along these lines. The problem with solely using a Check constraint and not actualy containing the data in my case is that I sometimes need access to the actual enumeration values, both the underlying integer and the token identifier of the enumeration. The table gives me access to this metadata quite nicely. Thanks for the help. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-07-12 : 23:46:57
|
| I wrote that reply before your other replies..Unhelpful hey....yeah...learning sucks..A check constraint is the ONLY way to implement a type constraint in SQL.. The ENUM data type in mysql is a shortcut for this type of constraint.. But you know that already don't you?The code table approach swaps a type constraint for a referential integrity constraint and swaps a type for a relation.. This should not be considered trivial and has performance and modelling issues..Why can't you use reflection to obtain the underlying integer value or identifer?DavidM"Always pre-heat the oven" |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-13 : 00:11:22
|
| The ONLY way to implement a type constraint? One way would be to define the column as a particular type, such as int or nchar, right? Actually a check constraint doesn't seem like a TYPE constraint at all to me (but I am an applications programmer and the semantics may well be different - help me if they are). It can mimic one, say by limiting the domain of valid input on an nvarchar type column to strings comprised of ([^0-9]+) or by ensuring that an integer can be converted to smallint, even thought the column is described as int or decimal. A type constraint seems like it would do just that, constraint the TYPE of data that can be stored in a particular slot (type being the operative keyword here, meaning a simple type such as int or decimal or nchar and we can even extend this definition to string (varchar)). This seems to me a different concept than constraining the DOMAIN of valid integers further than what the TYPE already constrains it to. Or even different than constraining the permutation of characters up to the column buffer's max length in a column that is designated to store values consistent with the varchar TYPE. Type constraint - isn't this what we do when we define a column? |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-13 : 00:27:49
|
| >> The ENUM data type in mysql is a shortcut for this type of constraint.. But you know that already don't you?No, I didn't know that, but I believe you because it makes sense to me from the standpoint that an enum in mysql would be represented under the covers as varchar with a check constraint of sorts applied to constrain the domain of valid values (or the permutations of characters allowed). But what I have problems with is that you seem to use constraint and type in an interchangeable fashion. If an ENUM is a constraint masquerading as a data type, then isn't that an implementation detail?How would you implement the requirements? Thanks for engaging me in this. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-07-13 : 01:10:45
|
| Type and Domain are interchangeable terms in the relational model. As I have said a type is a set of values. The values are up to you.The base types in most DBMSs are mostly useless on there own. Take the money type for example..Used in a salary attribute the negative representation is part of the money domain but is not part of the salary domain. Enter the CHECK constraint..Or my personal favourite.. a column marked as incrementing starting at 1 but the domain also allows negative values.There is always confusion between the logical and the physical representation of types. It is good to be aware of this fact. Conversion to and from types can appear handy, but can cause some serious problems in databases. As an example, I had the misfortune to correct a query that was joining an int with a decimal. The problem was it made no sense.. Joining a customerid to a supplierid.. A good DBMS with proper domain support would have never allowed it..I have seen a few database schemas in my life, and the good ones have a large number of constraints declared at DDL time..the bad ones always just have a primary key...DavidM"Always pre-heat the oven" |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-07-13 : 01:27:27
|
| The type is the fundamental constraint in a relation.The ENUM data type in mysql is a clever implementation of a CHECK constraint, but that is all it is.As for your original problem, I tend to go for CHECK constraints, but if you REALLY need more properties, like underlying integer etc.. then I would probably go with a table.. The next version of SQL will have decent UDTs so this whole conversion should be redundant..DavidM"Always pre-heat the oven" |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-13 : 01:41:16
|
| So you are saying that a constraint is a further specification of a type, not a business rule. So the constraint is an artifact of the db's implementation of the type? I guess I just differentiate between a simple type (like int) and what I would consider (although semantically wrong in the case of sql server) an UDT. Like you, I am excited about real udt support in coming versions of sql server.As for my problem, you would implement a check constraint as...where mycolumn in ('foo', 'fee', 'fi', 'fo')? And give up the ability to query the range of allowable values from the consuming application? I guess this is what I am so reluctant to give up. But I am trying to be convinced, because my code generators will work so much better if these types of things are implemented as constraints. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-13 : 01:43:09
|
| I use the ENUMS approach that you describe.1. Developers (hey, and end users too when appropriate) can add to the enums table more easily that fiddling with constraints2. The Enums table provides for a pick-list in the applicationKristen |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-13 : 01:47:17
|
quote:
The Enums table provides for a pick-list in the application
Yes, exactly. Exactly, exactly, exactly. This is what I am saying about having access to the metadata. I don't want to have to parse the text of the constraint to get this list, and I don't want to have to use extended properties of some sort. I want it available in the schema for easy access. And I like the approach of the enum table, whose third column specifies the 'type' it would be if it actually were a real enum. But I hear byrmol. I think the check constraint is more efficient, but hey, in the end I'm an applications programmer and not a dba, so if they want me to design the schemas too, and it comes down to old man Codd or old man Code, I'm in bed with old man Code. :) |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-07-13 : 09:22:26
|
| There was a discussion about that here and an implementation of constraints with some metadata that can be used. I will try to find it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-13 : 10:47:05
|
| Don't know if it applies to you too, but our ENUM table has an "alias" ability, so a Country Code can be "aliased" to be used by additional columns. (In fact we store Country Code in a table of its own, so we can use an FK, but I couldn't think of a real example offhand!)Kristen |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-07-13 : 16:57:13
|
| I started a mini flame war with this very topic..In my last gig this is what we did....All single property types where declared as a check constraint. The values are stored in a virtual view. Only DBAs can alter the check constraint or view. It was a very controlled environment, with some serious data reporting and mining ie Federal government policy decisions. It was my responsiblity for all of the domains integrity. No applcation developer was allowed near these values..For multiple property types, a table for each type was used with RI implemented.What language are using at the front chacha? Most decent 4GL have enum reflection built in...DavidM"Always pre-heat the oven" |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-13 : 19:01:55
|
| No, byrmol, not a flame war, a heated discussion. And you provided the pre-heat :)It sounds like the only difference is that instead of storing the values in a table, you stored them in a virtual view. This is acceptable to me. I am having the problem, though, that sql server won't allow subqueries in a check constraint - I guess that's because it essentially makes it a referential integrity constraint and there is a well established protocol for creating those. So I guess I should do what byrmol says? Or use a trigger that implements the constraint using a subquery?Byrmol - C# mostly and C++ for our network graphing algorithms |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-14 : 02:13:15
|
| I'm really not that fussed about a check constraint (maybe I should be!)All our data actions go through SProcs and Triggers and they are responsible for stopping any garbage reaching the database.(Obviously bulk import would be an issue, so we do that through a staging table, thus anything that gets thrown at the datbase direct is already proven clean)Kristen |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-14 : 02:39:47
|
| Kristen,What kind of performance issues do you experience when intercepting everything the way you do? That is why you do the bulk imports at the staging table? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-14 : 04:22:29
|
Not sure I understand the question ... but:Our application could do:UPDATE MyTable SET MyCol1='AAA', MyCol2='BBB' WHERE MyPKCol = 'FRED'orEXEC dbo.MyTableUpdateSProc @MyCol1='AAA', @MyCol2='BBB' @MyPKCol = 'FRED'we do the second, and it is way WAY faster than doing the first. ([1] should be preceeded by lots of checking, [2] has the checking built-in)For bulk data import we have "X" [transfer] tables that look just like the "real" tables but have very few constraints (Primary key maybe, that's all). Frequently columns are VARCHAR rather than INT/DATETIME and thus any old rubbish can be put in there; there are no NOT NULL columns. We have an extra column called "ErrorMsg" and we put in there "Column X is not datetime" and "PartNo not found in Products table" and so on.Then we push the "good" ones in the real table using a "delta" update:UPDATE DSTSET ColumnA = SRC.ColumnA ...FROM MyTable DST JOIN MyXTable SRC ON SRC.PK = DST.PKWHERE (SRC.ColumnA <> DST.ColumnA OR (SRC.ColumnA IS NULL AND DST.ColumnA IS NOT NULL) OR (SRC.ColumnA IS NOT NULL AND DST.ColumnA IS NULL)) OR (SRC.ColumnB ...INSERT INTO MyTable(ColumnA, ...)SELECT ColumnA, ...FROM MyXTable SRC LEFT OUTER JOIN MyTable DST ON DST.PK = SRC.PKWHERE DST.PK IS NULL Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-14 : 09:44:40
|
quote:
Originally posted by chachaThe Enums table provides for a pick-list in the applicationand it comes down to old man Codd or old man Code, I'm in bed with old man Code. :)
NoooooooooooooooooooooooooooooooooooThat's sacreligious....Edgar's turning over right now....If you have an opportunity....grab it....Brett8-) |
|
|
|
Next Page
|
better way to represent enums in sql server?