Why bother? Just store it as a complete date (perhaps always using the first as the day) and use the database functions MONTH() and YEAR() if you only need part of it. This makes using that field much easier as you can still do range queries, etc.
Roles should be tracked separately from user accounts, because someone can be promoted (or demoted) over time. Would it make sense in that situation to have two different user accounts, in two different tables? I think not. Here’s the basic structure I’d use – USERS user_id (primary key) user_name ROLES role_id (primary key) role_name USER_ROLES … Read more
ALTER TABLE DROP COLUMN does just only disabling columns in system tables. It is very fast, but it doesn’t remove data from heap files. You have to do VACUUM FULL later to compact allocated file space. So ALTER TABLE DROP COLUMN is very fast. And to compact files, you have to call the slower (with … Read more
There can only be one primary key per table – as indicated by the word “primary”. You can have additional UNIQUE columns like: CREATE TABLE test( sl_no int PRIMARY KEY, — NOT NULL due to PK emp_id int UNIQUE NOT NULL, emp_name text, emp_addr text ); Columns that are (part of) the PRIMARY KEY are … Read more
With the proviso that I haven’t used Datomic in production, thought I’d give you an answer. Advantages Datalog queries are powerful (more so than non-recursive SQL) and very expressive. Queries can be written with Clojure data structures, and it’s NOT a weak DSL like many SQL libraries that allow you to query with data structures. … Read more
Technically yes, but if a string makes sense to be the primary key then you should probably use it. This all depends on the size of the table you’re making it for and the length of the string that is going to be the primary key (longer strings == harder to compare). I wouldn’t necessarily … Read more
UUIDs Unless these are generated “in increasing monotonic sequence” they can drastically hurt/fragment indexes. Support for UUID generation varies by system. While usable, I would not use a UUID as my primary clustered index/PK in most cases. If needed I would likely make it a secondary column, perhaps indexed, perhaps not. Some people argue that … Read more
I’ve seen people in my Google searches about schemas make a separate schema+tables for each online customer to their website. They have like 100,000 schemas. Q3: What am I missing here? This seems extreme to say the least. They should be adding record(s) to standard table(s) for each customer not making schemas and tables for … Read more
If the view is indexed then any queries that can be answered using the index only will never need to refer to the underlying tables. This can lead to an enormous improvement in performance. Essentially, the database engine is maintaining a “solved” version of the query (or, rather, the index of the query) as you … Read more
Here is how I ended up solving this problem. I decided to use a dictionary to store data that is unique to each notification type. I then serialize that dictionary object into a binary string and store that in the database along with each notification. I have a template assigned to each notification type that … Read more