As a corollary to this question I was wondering if there was good comparative studies I could consult and pass along about the advantages of using the RDMBS do the join optimization vs systematically denormalizing in order to always access a single table at a time. Specifically I want information about : Performance or normalisation v...
Hi all, My first question on here so be nice! I am a junior developer with not much experience and am having trouble with this problem. I have a table which needs to be auditable. Let's say this table records phone calls made by a call centre (it's not, but it's just an example). I'll call it "CallHistory". I had originally planned to...
I'm trying to make a judgment call in implementing a small-ish SQL Server '08 database. I'm translating an output text file of a flat-file database from an old COBOL system to the aforementioned SQL Server database. It's a database of vehicle and real estate loans, which can be uniquely identified by the combination of a Lender ID (a s...
So frequently I find myself writing code like this: song.rb: :before_save :cache_sortable_name private def cache_sortable_name return unless name_changed? self.sortable_name = name.sub(/^(the|a|an)\s+/i, '') end I.e., I have a sortable_name database column which holds denormalized data for convenience, and I want to populate it...
Say I offer user to check off languages she speaks and store it in a db. Important side note, I will not search db for any of those values, as I will have some separate search engine for search. Now, the obvious way of storing these values is to create a table like UserLanguages ( UserID nvarchar(50), LookupLanguageID int ) but the s...
I'm using Rails and MySQL, and have an efficiency question based on row counting. I have a Project model that has_many :donations. I want to count the number of unique donors for a project. Is having a field in the projects table called num_donors, and incrementing it when a new donor is created a good idea? Or is something like @num...
I've started a new project and they have a very normalized database. everything that can be a lookup is stored as the foreign key to the lookup table. this is normalized and fine, but I end up doing 5 table joins for the simplest queries. from va in VehicleActions join vat in VehicleActionTypes on va.VehicleActionTypeId equals...
The current situation is that topics are sorted by 3 main categories. There is the potential to add more than just the 3 categories but the higher ups wish to implement the ability to add more than just 1 category to a topic. My original db design has the the categoryID as a Foreign Key in the topic info table. Which was probably a ...
Overview (Sorry its vague - I think if I went into more detail it would just over complicate things) I have three tables, table one contains an id, table two contains its own id and table one's id and table three contains its own id and table two's id. I have spent a lot of time pondering and I think it would be more efficient for tabl...
I am in the early stages of developing a database-driven system and the largest part of the system revolves around an inheritance type of relationship. There is a parent entity with about 10 columns and there will be about 10 child entities inheriting from the parent. Each child entity will have about 10 columns. I thought it made sense ...
Hello All I hope you can help find an answer to a problem that will become a recurring theme at work. This involves denormalising data from RDBMS tables to flat file formats with repeating groups (sharing domain and meaning) across columns. Unfortunately this is unavoidable. Here's a very simplified example of the transformation I'd re...
Ok, suppose to have this db schema (relation): |User | (1-->n) |Customer | (1-->n) |Car | (1-->n) |Support | |--------| |---------| |-----| |-----------| |id | | user_id | |Brand| |Description| |username| |lastname | |PS | |Cost | |password| ...
I have a Linq-To-Sql mapping between POCO classes and my DB. I want to be able to add properties on my classes which represent slightly more complex constructs than simple scalr values. For instance, I have a custom struct type which holds two simple scalar values. I don't want to make this another table and then add a FK as the propert...
In my Rails application, I have a variety of database tables that contain user data. Some of these tables have a lot of rows (as many as 500,000 rows per user in some cases) and are queried frequently. Whenever I query any table for anything, the user_id of the current user is somewhere in the query - either directly, if the table has ...
When it comes to denormalizing data in a transactional database for performance, there are (at least) three different approaches: Push updates through stored procedures which update both the normalized transactional data and the denormalized reporting/analysis data; Implement triggers on the transactional tables that update the seconda...
I am quite ashamed to ask this, but recently there has been a situation where I need to create a single table for three different types of banking entities that are related to each other. Let me explain. Imagine a BANK table that holds details of either a Governing Bank, or a regular Bank that operates rural branches, or the rural branc...
My question may probably be an echo of a question previously asked here: http://stackoverflow.com/questions/452295/how-to-design-a-user-object-model-using-ms-roles-membership, but I'm asking again since there is no good answer yet and my question is geared toward the data model design anyway: So anyway, in my application, there are user...
My web app deals with polls (surveys). Right now I have 2 tables as part of database schema. polls id question choices (ex: yes,no,maybe) created polls_responses poll_id user_id tracker_id response The problem with this is that on some polls I have alot of responses (>1000). People can view the resul...
I heard a lot about denormalization which was made to improve performance of certain application. But I've never tried to do anything related. So, I'm just curious, which places in normalized DB makes performance worse or in other words, what are denormalization principles? How can I use this technique if I need to improve performance?...
I am working on an auction web application. Now i have a table with bids, and from this table i want to select the last 10 bids per auction. Now I know I can get the last bid by using something like: SELECT bids.id FROM bids WHERE * GROUP BY bids.id ORDER BY bids.created Now I have read that setting an amount for the GROUP BY results ...