database-design
Sane way to store different data types within same column in postgres?
You’ve basically got two choices: Option 1: A sparse table Have one column for each data type, but only use the column that matches that data type you want to store. Of course this leads to most columns being null – a waste of space, but the purists like it because of the strong typing. … Read more
Add ASP.NET Membership tables to my own existing database, or should I instead configure a separate ASP.NET membership database?
I personally just added the asp.net membership stuff to my own database. I then wrote a basic wrapper class around System.Web.Security.Membership class so that the code acts like its using its own membership stuff. Its pretty slick and not that hard to do. If you need assistance setting it up, here is what I did. … Read more
Database table design for scheduling tasks
This is the table structure i came up with; Schedule – ScheduleName – ScheduleTypeId (Daily, Weekly, Monthly, Yearly, Specific) – StartDate – IntervalInDays – Frequency – FrequencyCounter ScheduleDaily – ScheduleDailyId – ScheduleId – TimeOfDay – StartDate – EndDate ScheduleMonthly – ScheduleMonthlyId – ScheduleId – DayOfMonth – StartDate – EndDate ScheduleSpecific – ScheduleSpecificId – ScheduleId – … Read more
Why single primary key is better than composite keys?
I don’t think there is a blanket statement that you should only ever use a single primary key named id. Most people use a surrogate primary key as an auto generate int, because it isolates the primary key from ever needing to be changed, like if you make the PK the user name and they … Read more
MongoDB structure: single collection vs multiple smaller collections
I would recommend NOT to make separate collection per user. Read the documentation By default MongoDB has a limit of approximately 24,000 namespaces per database. Each namespace is 628 bytes, the .ns file is 16MB by default. Each collection counts as a namespace, as does each index. Thus if every collection had one index, we … Read more
What is the recommended equivalent of cascaded delete in MongoDB for N:M relationships?
What you are doing is the best and most optimal way of doing it in Mongo. I am in a similar situation and after going all possible implementations of the N:M design pattern, have also arrived to this same solution. Apparently, This is not a mongodb thing, but more of a concept of NoSQL, wherein, … Read more
why varbinary instead of varchar [duplicate]
Mediawiki changed from varchar to varbinary in early 2011: War on varchar. Changed all occurrences of varchar(N) and varchar(N) binary to varbinary(N). varchars cause problems (“Invalid mix of collations” errors) on MySQL databases with certain configs, most notably the default MySQL config.
Choice of Database schema for storing folder system
Your first schema will work just fine. When you put an index on the FullPath column, use either the case-sensitive BETWEEN operator for queries, or use LIKE with either COLLATE NOCASE on the index or with PRAGMA case_sensitive_like. Please note that this schema also stores all parents, but the IDs (the names) are all concatenated … Read more