![]() ![]() So, unless you've revised your original suggestion, let's assume the datatype and length issues have been settled - char(1). I think that's a very good idea, and I've been using a similar approach for years. I'm involved in a large database development project and I'd like to find out your opinion on best practices for what I'm referring to as "pseudo-boolean datatypes".Īs mentioned in this thread, Oracle does not natively support an SQL boolean datatype, and you've suggested an alternative implementation using a char(1) datatype with a check constraint. This is a follow-on to the current thread. ![]() Best I've been able to come up with is "Because they don't _need_ it, and because they don't feel like having it." You have to admit, that's a pretty rare answer to give w/r/t an Oracle feature. Sorry if this sounds whiney, but it's something I find myself explaining to nearly every new Oracle developer, and I've never heard a really good reason for why not. Space: if I'm using a fixed width multi-byte character set, doesn't a char(1) boolean take up more space than a universal boolean would? Speed: doesn't that check constraint take a tiny amount more time than validating against conformance to a particular datatypes? PL/SQL compatibility: It would be nice to be able to declare booleans using "var_name%TYPE".Īnd just to clarify on performance issues: Someone who spoke another language might choose two different characters. When I needed my first one, I used T and F. ![]() It would be nice to not have to make that conversion every time.Ĭonsistency: Y/N values with a check constraint are one way to represent a boolean. Sure, if it's a char(1) that's a good indication, but not a sure-fire one.Ĭompatibility: I know you're suspicious of products developed for multiple platforms, but there are a lot of them out there. Ultimately, it depends on the project's specific requirements and the tradeoffs you're willing to accept.Clarity: if the value I'm trying to store really is a boolean, then when I'm glancing at my model, it's clear that that's what the column is. ConclusionĪs a developer, it is always essential to understand the available options and make informed decisions based on the project's specific requirements. This is another reason why using a slightly larger column doesn't matter that much, as it's unlikely to be indexed. Most posts won't be archived, so querying for posts that aren't archived will not use the index. ![]() If we decide to put an index on the is_archived column, it is unlikely to be selective enough to benefit the query. In a large database, the extra space usage can be significant but it usually doesn't make too much of a difference. As a result, when using timestamps, we need to be mindful of the amount of storage space we use. While using timestamps provides us with extra information, it has a few tradeoffs that we must consider.Ī timestamp column uses four times more storage space than a boolean column, which uses only one byte. However, the archived_at column provides us with more information, namely the time at which the post was archived, which could be useful in the long run. SELECT * FROM posts WHERE is_archived = false ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |