Table Naming: Underscore vs Camelcase? namespaces? Singular vs Plural?
Being consistent is far more important than what particular scheme you use.
database-design
Index on multiple columns in Ruby on Rails
The order does matter in indexing. Put the most selective field first, i.e. the field that narrows down the number of rows fastest. The index will only be used insofar as you use its columns in sequence starting at the beginning. i.e. if you index on [:user_id, :article_id], you can perform a fast query on … Read more
When I should use one to one relationship?
1 to 0..1 The “1 to 0..1” between super and sub-classes is used as a part of “all classes in separate tables” strategy for implementing inheritance. A “1 to 0..1” can be represented in a single table with “0..1” portion covered by NULL-able fields. However, if the relationship is mostly “1 to 0” with only … Read more
Best way to store time (hh:mm) in a database
You could store it as an integer of the number of minutes past midnight: eg. 0 = 00:00 60 = 01:00 252 = 04:12 You would however need to write some code to reconstitute the time, but that shouldn’t be tricky.
What columns generally make good indexes?
Indexes can play an important role in query optimization and searching the results speedily from tables. The most important step is to select which columns are to be indexed. There are two major places where we can consider indexing: columns referenced in the WHERE clause and columns used in JOIN clauses. In short, such columns … Read more
Is a one column table good design? [closed]
In terms of relational algebra this would be a unary relation, meaning “this thing exists“ Yes, it’s fine to have a table defining such a relation: for instance, to define a domain. The values of such a table should be natural primary keys of course. A lookup table of prime numbers is what comes to … Read more
Why use multiple columns as primary keys (composite primary key)
Your understanding is correct. You would do this in many cases. One example is in a relationship like OrderHeader and OrderDetail. The PK in OrderHeader might be OrderNumber. The PK in OrderDetail might be OrderNumber AND LineNumber. If it was either of those two, it would not be unique, but the combination of the two … Read more
When to use a View instead of a Table?
Oh there are many differences you will need to consider Views for selection: Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema Views can model complex joins easily. Views can hide database-specific stuff from you. E.g. if you need to do some checks using Oracles SYS_CONTEXT … Read more
Nullable Foreign Key bad practice?
No There is nothing wrong with Nullable FKs. This is common when the entity the FK points to is in a (zero or one) to (1 or many) relationship with the primary Key referenced table. An example might be if you had both a Physical address and a Mailing address attribute (column) in a table, … Read more
Best design for a changelog / auditing database table? [closed]
In the project I’m working on, audit log also started from the very minimalistic design, like the one you described: event ID event date/time event type user ID description The idea was the same: to keep things simple. However, it quickly became obvious that this minimalistic design was not sufficient. The typical audit was boiling … Read more