I'm doing some research into databases and I'm looking at some limitations of relational DBs. I'm getting that joins of large tables is very expensive, but I'm not completely sure why. What does the DBMS need to do to execute a join operation, where is the bottleneck? How can denormalization help to overcome this expense? How do other ...
I'm architecting a new app at the moment, with a high read:write ratio. At my current employer we have lots of denormalised data on our tables for performance reasons. Is it better practice to have totally 3NF tables and then use indexed views to do all the denormalisation? Should I run queries against the tables or views? An example of...
I'm looking for a strategy in as far as stopping the repetitive problem of branching out tables. For example as a fictitious use case, say I have a table with users that contains their name, login, password and other meta data. In this particular scenario, say the user is restricted to login per a specific subset of IP(s). Thus, we have ...
Normalization leads to many essential and desirable characteristics, including aesthetic pleasure. Besides it is also theoretically "correct". In this context, denormalization is applied as a compromise, a correction to achieve performance. Is there any reason other than performance that a database could be denormalized? ...
I have an ODBC connection to a database I don't own and can't change. What I am looking to do is to make related records merge into one record. The relationship is a 1 to many. I have a student managment system and want to export a call out list which feeds an automated callout service (charged by Call). I want to be able to call a ...
hi all, while trying to figure out how to tag a blog post with a single sql statement here, the following thought crossed my mind: using a relation table tag2post that references tags by id as follows just isn't necessary: tags +-------+-----------+ | tagid | tag | +-------+-----------+ | 1 | news | | 2 | top-story ...
I am working on a web API for the insurance industry and trying to work out a suitable data structure for the quoting of insurance. The database already contains a "ratings" table which is basically: sysID (PK, INT IDENTITY) goods_type (VARCHAR(16)) suminsured_min (DECIMAL(9,2)) suminsured_max (DECIMAL(9,2)) percent_premium (DECIMAL(9,...
My employer, a small office supply company, is switching suppliers and I am looking through their electronic content to come up with a robust database schema; our previous schema was pretty much just thrown together without any thought at all, and it's pretty much led to an unbearable data model with corrupt, inconsistent information. T...
I have a DataSet with some DataTables that are linked together with DataRelations (classic order Header/Detail pair). Is there an easy way to denormalize the whole lot into a single DataTable with all the columns of the related tables? The table names and columns are not known at compile time, and there may be more than two tables/relat...
My software went in production some days ago and now I want to argue a bit about the database structure. The software collects data about ships, currently 174 details for each ship, each detail can be a text value, a long text value, a number (of a specified length, with or without a specified number of decimals), a date, a date with ti...
I'm pondering the use of NHibernate on a project that has a database with some degree of planned denormalization (planned by the DBAs). Reading from one set of tables and mapping one column to one property is not a problem. However when updating I'd have to map one property back to the original column in the original table plus update a ...
I am working on a website with a simple normalized database. There is a table called Pages and a table called Views. Each time a Page is viewed, a unique record of that View is recorded in the Views table. When displaying a Page on the site, I use a simple MySQL COUNT() to total up the number of Views for display. Database design seem...
I was wondering is this a good design, assuming the tables as follows ADDRESS(id, address, city_fk, stateFK, countryFK), CITY(id, name, stateFK, countryFK), STATE(id, name, countryFK), COUNTRY(id, name) Notice how country fk is repeated in 3 tables? and state fk repeated in 2 tables? Can anyone tell me if this is a good design? If s...
Hi, I'm hoping someone can provide some advise for an easier way to deal with this problem. I am working on creating a flattened view of a highly normalized set of data. The goal of flattening is to provide a view which normal people can use to develop reports from. The source data contains a couple of tables as shown: CREATE TABLE ...
I'm looking to introduce some database denormalization to a heavily normalized system. I have a large set of databases that have evolved over ten years and are under increasing amounts of load so I'm looking to improve PERFORMANCE and possibly reduce the complexity of some queries. It is not uncommon to do 10 joins to accomplish any ...
Hi, We're about to run side-by-side testing to compare a legacy system with a new shiny version. We have an Oracle database table, A, that stores data for the legacy system, and an equivalent table, B, that stores data for the new system, so for the duration of the test, the database is denormalized. (Also, the legacy system and table A...
Suppose I have a table of customers: CREATE TABLE customers ( customer_number INTEGER, customer_name VARCHAR(...), customer_address VARCHAR(...) ) This table does not have a primary key. However, customer_name and customer_address should be unique for any given customer_number. It is not uncommon for this table to con...
Before denormalizing, I'm wondering what effect this is going to have on the following: Query response time Width of rows in the database Joins necessary for a result Number of queries necessary for requests to complete It seems, if I am not mistaken, that all of these will be reduced? ...
Hi folks, I've got a database in production for nearly 3 years, on Sql 2008 (was '05, before that). Has been fine, but it isn't very performant. So i'm tweaking the schema and queries to help speed some things up. Also, a score of main tables contain around 1-3 mill rows, per table (to give u a estimate on sizes). Here's a sample datab...
what is the best practice for database design for high traffic websites like this one stackoverflow? should one must use normalize database for record keeping or normalized technique or combination of both? is it sensible to design normalize database as main database for record keeping to reduce redundancy and at the same time maintain...