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? ...
If I am posting a question about a query against an Oracle database, what should I include in my question so that people have a chance to answer me? How should I get this information? Simply providing the poorly performing query may not be enough. ...
What is the best method to determine if a user has viewed a piece of data, ie like an update to a comment. The two solutions I have thought about are these.... Use a separate table that has a row for each user and the data id that is being viewed and inserting into when the item was last viewed. Use the same table and add a row for eve...
Hi folks, Overview I'm working on some Emergency Services reporting and mapping application for California (kind of weird, considering the fires there, right now...). We need to map demographic and emergency data for an internal govt unit. What we have are all the streets, cities and neighborhoods in California. Each neighborhood also...
I'm working on a star schema for analysis of posted form data. The site that the form data will be posted to is actually external to the site hosting the form, so only the data in the form will be available. I'm going to give the option to include some extra useful information with hidden fields, original referrer, session id etc. I'll ...
I have a list of tables i.e. student, teacher, staff, dept. and so on and each of these tables have comments specific to them. Now one record in a table can have one or many comments that shows it's a one to many relation from any table to comments table. I don't know what the best way is to relate comments table to each of these. If I p...
I'm not sure what this practice is actually called, so perhaps someone can edit the title to more accurately reflect my question. Let's say we have a site that stores objects of different types. Each type of object has its own database (a database of books and assorted information with its tables, a database of CDs and information with ...
I'm currently designing a database schema that's used to store recipes. In this database there are different types of entities that I want to be able to tag (ingredients, recipe issuers, recipes, etc). So a tag has multiple n:m relations. If I use the "three table design", this would result in tables (cross table) for every entity type ...
When designing a lookup table (enum) in SqlServer 2005, if you know the number of entries will never get very high, should you use tinyint instead of int? I'm most concerned about performance, particularly efficiency of indexes. Let's say you have these representative tables: Person ------ PersonId int (PK) PersonTypeId tinyint (FK ...
I have a polymorphic association in a Ruby on Rails model. In the migration, I have: create_table "offer_defs" do |t| t.integer "product_def_id" t.string "product_def_type" ... end I would like to add an index for this association. I am hesitating between the following options: add_index :offer_defs, [:product_def_id, :produ...
I'm wondering if this is a good design. I have a number of tables that require address information (e.g. street, post code/zip, country, fax, email). Sometimes the same address will be repeated multiple times. For example, an address may be stored against a supplier, and then on each purchase order sent to them. The supplier may then...
How would make a news feed "friendly" database design, so that it wouldn't be extremely expensive to get all of the items (query) to put in the news feed? The only way I can think of would involve UNIONing nearly every table (representing groups, notes, friends, etc) and getting the dates and such, that just seems like it'd be a really ...
Are there any good references for best practices for storing postal addresses in an RDBMS? It seems there are lots of tradeoffs that can be made and lots of pros and cons to each to be evaluated -- surely this has been done time and time again? Maybe someone has at least written done some lessons learned somewhere? Examples of the tra...
In my application I have a Customer class and an Address class. The Customer class has three instances of the Address class: customerAddress, deliveryAddress, invoiceAddress. Whats the best way to reflect this structure in a database? The straightforward way would be a customer table and a separate address table. A more denormalized ...
I have a system that creates an order and that order can be billed to a house account, sent Cash on Delivery (COD), or charged to a credit card. I've created the following tables: ORDERS order_id billingoption_id BILLINGOPTIONS billingoption_id I'm unsure of how the next table should be built for the billing data. Should I build a s...
We're writing a records management product for schools and one of the requirements is the ability to manage course schedules. I haven't looked at the code for how we deal with this (I'm on a different project at the moment), but nonetheless I started wondering how best to handle one particular part of this requirement, namely how to hand...
I have long considered the design of a database that involves shared table purposes to be somewhat a trait of smelly code, and progressively increasing proliferation of smelly-code related problems. By this I mean, people over-normalizing, using 1 table where 2 tables could be more logical, people who've just discovered what normalizing...
The UK VAT system is changing from 17.5% to 15%. What strategies have you used in your code to store the VAT, and how will the change affect your applications. Do you store a history of vats so you can calculate old prices, or are old invoices stored in a separate table? Is it a simple config setting, or did you bodge it? What's the idea...
I'm developing a multilanguage software. As far as the application code goes, localizability is not an issue. We can use language specific resources and have all kinds of tools that work well with them. But what is the best approach in defining a multilanguage database schema? Let's say we have a lot of tables (100 or more), and each ta...
I have a table which consists of 200 Companies Stock prices for 5 years. This is one large table which consists of Company Name, Stock Open, High, Low, Close, Date I am now required to do some processing on the same and also let users [up to 10] access this database to fetch reports on different sets of parameters and queries. Should I...