Tag Info

New answers tagged

0

I came up with a "hybrid" solution. And you may give it a thought. I understand it's not desireable to modify 3rd party database, or, sometime it is not even possible because you don't have access to client sources, right? In my case, source data did not have anything I can "reference" like "updatedOn" or any reliable timestamps or logs.. I also needed to ...


-2

Double entry handles split transactions well. Credit checking 200 and debit grocery 150 debit household 50 Debits = Credits. The double entry means recording both debit(s) and credit(s).


0

Quit. Honestly, you can spend months arguing about normalization, consistency, and fighting crazy bugs caused by sheer laziness, and then quit. Or you can just save time, and frustration and quit now. Good programmers are very lazy people. They understand customer and management needs. But most importantly they understand that solving problems well, ...


0

To add to what the other guys have suggested above. This is a data governance issue. You need to work with relevant stakeholders: data architects and data stewards to develop data principles, policies and naming conventions. Be patient and work methodically. Change won't happen over night.


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 - ...


0

One approach would be to design your database similar to how you would define a class hierarchy, where you define base table(s) (classes) that provide the common attributes, and then you add additional tables that provide specific attributes to extend the table to the specific products. Define a base product table create table Products_base as id ...


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


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 ...


0

Don't add isgroup to your person table. Make a group table as its own entity. Role-based security model: person, role, group, roleonGroup, roleonUser, permission, itemPermission, itemPermissiononRole or person, role, group, roleonEntity, permission, itemPermission, itemPermissiononRole where 'entity' can be a person or group. I prefer the former even ...


0

For Option 1, you appear to be missing the data about which people are in each group, and now every query on the Person table has to know about these fake "group people", even ones that don't care about sharing or groups. Option 2, by tying the person->group mapping to the share->person mapping (which really have nothing to do with each other), is ...


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 ...


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 ...


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 ...


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.


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 ...


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' ...


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 ...


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 ...


6

No, it's not. Think about it, where would you ever need them? Not when you create the model: you know what model are you creating. Not inside the model itself: you know where you are too. Nor when accessing the model later: you still know the type of the model, unless you don't care about the type, in which case prefixes will be only an annoyance. ...


4

Design your database around the data. What I mean by this is: You have users You have posts You have comments You have users you are following You have notifications It looks like you are trying to design your database around one use case rather than better organizing your data. Try something like: CREATE TABLE Users( id int not null auto_increment, ...


0

One way to do that : Create a new table , Name it something like : Followers with a composite primary key and reference the foreign keys to your 'Notifications" table Something like: CREATE TABLE Follower ( userid integer, Followeruserid integer, primary key (userid, Followeruserid) );


0

As noted by John F. Miller on another question at SO (http://stackoverflow.com/a/5373969/1793074), MongoDB is great at nesting data, but not so grat to search this nested data. Another great resource on this matter comes from the same answer: http://seanhess.github.io/2012/02/01/mongodb_relational.html Basically, you relinquish control when you work with ...


-1

I try to avoid stored proc's if at all possible. But when I have to for security, my goal is a compromised approach. I use stored proc's for updating commands (INSERT, UPDATE, DELETE) and the ORM for everything else. Further, each stored proc does a single operation on a single table. The application code coordinates all of the calls and manages the ...


0

Modern Object Relation Mapping libraries, and even the lower level libraries allowing you to run SQL against the database, have guards in place to prevent SQL injection attacks. To use stored procedures because all other solutions are not secure is both short sighted and false. If you must hard code SQL, you can use parameterized queries, which guard against ...


2

Sprocs are very good for implementing a secure data access layer - you write sprocs for reading and writing data, and give the client execute access to the sprocs only - no access to the underlying tables or views. This gives your DB an API that clients use, in much the same way as any class implemented in your business logic code would, but much more ...


0

It's been six months since I asked this question and the result is that we did kind of compromise between the custom CMS and our web application. We introduced a few new fields in the CMS that would enable us to query the database in more reliable and flexible manner. After that we introduced a few new queries that help us getting the data dynamically. This ...



Top 50 recent answers are included