Tag Info

Hot answers tagged

125

Your operational database should be highly normalized, to reduce anomalies. Your analytic database (warehouse) should be highly denormalized, to ease analysis. If you don't have a separate analytic database, you should make some highly denormalized [materialized] views. If you tell your senior business analysts / managers to do lots of joins for a ...


57

I understand, why somebody wants to avoid writing a join for each select. But you can create once a view with the join and use it instead of your unnormalized table. So you combine the advantage of normalization with the convenience of an easy select.


14

The answers which have already been upvoted pretty much cover the "how to avoid duplication" (using views) but not the why. They basically show that duplication of columns is the wrong solution to the problem of making it easier to write queries. But the question "why not duplicate any random column just for the heck of it?" still stands. The answer is ...


12

Thinking of it in terms of tradeoffs rather than good/bad will be more productive. They are trading off advantages of normalization (esp. consistency) for advantages in query usability. At one extreme, the database would become useless if the data got severely inconsistent. At the other extreme, the database would be useless if it's too difficult for the ...


8

The technique I have normally used is to save the complete record, with an end_timestamp field. There is a business rule that only one row can have a null end_timestamp, and this is of course the currently active content. If you adopt this system, I strongly recommend you add an index or constraint to enforce the rule. This is easy with Oracle, as a unique ...


7

A bit about relational databases One of the most powerful features of relational databases is the ability to connect sets of data through common points. In order to do this efficiently, a database should follow the rules of normalization. To sum those rules up, a database should have: No repeating elements or groups of elements No partial dependencies on ...


6

I think the strongest argument for data normalization for business analysts is that it promotes data integrity. If your key data is stored in only one place (one column, in one table), it's much less likely that the data will get corrupted by incorrect updates. I think they would probably care about the importance of data integrity, so this this might be a ...


6

Note that if you use Microsoft SQL Server, there is already a feature for that called Change Data Capture. You still will need to write code to access the previous revisions later (CDC creates specific views for that), but at least you don't have to change the schema of your tables, nor implement the change tracking itself. Under the hood, what happens is ...


4

Solve the problem "philosophically" and in code first. And then "negotiate" with code and database to make it happen. As as example, if you're dealing with generic articles, an initial concept for an article might look like this: class Article { public Int32 Id; public String Body; } And at the next most basic level, I want to keep a list of ...


3

Do not make a table for every product. This problem has been solved many ways. Try this: Make a product (or products) table, put your common product attributes in the product table, then make an attribute table and a productattribute table, something like: attribute --------- attributeId attributeName attributeDescription productattribute ...


2

There's a PostgreSQL wiki page for an audit tracking trigger which walks you through how to set up an audit log that will do what you need. It tracks the full original data of a change, as well as the list of new values for updates (for inserts and deletes, there's only one value). If you want to restore an old version, you can grab the copy of the original ...


1

Assuming that you're already in this situation: Store form config in a DB and reference the version number, rather than serialize it to Json over and over again. Don't use conditionals, just have completely separate html/js pairs and backend end points. You might have to do some internal rewrite if you want to have the forms appear under a single url. ...


1

Option 3) Snapshots on the Hour (or some interval) + the change log. Best of both worlds. Quick build Process a max of 1 hour Changes Low(er) storage


1

Since Likes and Stars(favorite) pertain to the same object, a "post". A unifying domain could be used to store Likes and Stars such as postIcons: {userId: '123', postId:'789', like: true, star: true}. You would have an entry for every post your user liked or favorited but would only have one record for a post your user liked and favorited. 'Followee' ...


1

Perhaps Liquibase can help with its include tag. Put the more common table definitions in separate changesets. Then include these in the more specific ones. This allows you to maintain the different levels of tables separately, but generate specific databases as required. Plus it adds all the versioning options of Liquibase. For more information see the ...


1

Several things in your diagram seem to be missing or wrong. Do you really want to constrain employees to work on just one project? Do you want to allow that employees work on a project of another department? If not, you have to add a constraint/invariant prohibiting this. With your current multiplicities for the Employee-manages-Department association you ...


1

Here's the highlights from research from other sources (originally from revision 2 of the question): Should I use one database per application or share a single database amongst multiple applications Supporting multitenancy MSDN Multi-Tenant Data Architecture SO How to create a multi-tenant database with shared table structures? CloudExpo SaaS - ...



Only top voted, non community-wiki answers of a minimum length are eligible