My table has two columns: startsAt endsAt Both hold date and time. I want to make following constraint: IF both columns are NOT NULL then range between startsAt and endsAt must not overlap with other ranges (from other rows). Answer You can keep your separate timestamp columns and still use an exclusion const…
Tag: database-design
SQL one to one relationship vs. single table
Consider a data structure such as the below where the user has a small number of fixed settings. User UserSettings Is it considered correct to move the user’s settings into a separate table, thereby creating a one-to-one relationship with the users table? Does this offer any real advantage over storing …
Should I create separate SQL Server database for each user?
I am working on Asp.Net MVC web application, back-end is SQL Server 2012. This application will provide billing, accounting, and inventory management. The user will create an account by signup. just …
Relationship of Primary Key and Clustered Index
Can a TABLE have a primary key without a clustered index? And can a TABLE have a clustered index without having a primary key? Can anybody briefly tell me the relationship between primary key and …
Why database designers do not make IDENTITY columns start from the min value rather than 1?
As we know, In Sql Server, The IDENTITY (n,m) means that the values will start from n, and the increment value is m, but I noticed that all database designers make Identity columns as IDENTITY(1,1) , …
MySQL PRIMARY KEYs: UUID / GUID vs BIGINT (timestamp+random)
tl;dr: Is assigning rows IDs of {unixtimestamp}{randomdigits} (such as 1308022796123456) as a BIGINT a good idea if I don’t want to deal with UUIDs? Just wondering if anyone has some insight into …
SQL: Advantages of an ENUM vs. a one-to-many relationship?
I very rarely see ENUM datatypes used in the wild; a developer almost always just uses a secondary table that looks like this: But the same thing can also be shown using a user-defined type / ENUM: (Example shown using PostgreSQL, but other RDBMS’s have similar syntax) The biggest disadvantage I see to …
Name database design notation you prefer and why?
Which notation, methodology and tools for database designing, modeling, diagraming you prefer and why? Which notation, standards, methodology are the most broadly used and covered by different vendors? Which are standard and which are not? i.e. which are to stick with and which to avoid? And why do you prefer…
Use email address as primary key?
Is email address a bad candidate for primary when compared to auto incrementing numbers? Our web application needs the email address to be unique in the system. So, I thought of using email address …
How to search for a substring in SQLite?
Whats the most efficient way to search for a sub string in SQLite? I’m looking at the LIKE operator. Do I have the right idea? Has this worked well for you? http://www.sqlite.org/lang_expr.html …