I have some good books on database theory (covering relational calculus, tuples, normal forms etc.) but I'd like a nuts-and-bolts text on appropriate database design: appropriate schema design choices and practical issues that come up in the field. Not so much theory oriented but from the perspective of being in the trenches. Could y...
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...
What is the best way to design the schema for the following requirements? Need to store Countries, States, Counties, and counties can be divided into regions. Regions can then have people with various data points to report on. Regions can also be further divided into divisions which is like grouping people. So Region 1 can have Divis...
Hi I've heard that on stackoverflow questions and answers are stored in the same DB table. If you were to build a twitter like service that only would allow 1 level of commenting. ie 1 tweet and then comments/replies to that tweet but no re-comments or re-replies. would you use two tables for tweets and retweets? or just one table whe...
I have to make some changes in a small system that stores data in one table as following: TransId TermId StartDate EndDate IsActiveTerm ------- ------ ---------- ---------- ------------ 1 1 2007-01-01 2007-12-31 0 1 2 2008-01-01 2008-12-31 0 1 3 2009-01-01 2009-12-31 1 1 4...
I have two distinct (no inheritance) interfaces: IInvestable and IPricable and two classes: IndexClass and FundClass that are stored in seperate tables in my DB. IndexClass is IPriceable FundClass is IPriceable and IInvestable. I need a table to store prices against an IndexClass and a FundClass I need a table to store that FundClass...
I have three tables: Users, Companies and Websites. Users and companies have websites, and thus each user record has a foreign key into the Websites table. Also, each company record has a foreign key into the Websites table. Now I want to include foreign keys in the Websites table back into their respective "parent" records. How do I d...
I am writing a web application, that is US specific, so the format that other countries use for postal codes are not important. I have a list of us zip codes that i am trying to load into a database table that includes the 5 digit us zip code latitude longitude usps classification code state code city the zip code is the primary ke...
In my database there are four databases: Teachers, Students, Parents and Addresses. Teachers, students, parents can have any number of addresses (zero or more). I would like to use foreign keys to link between teachers and their addresses (and between students and their addresses etc). Since records in Addresses can originate in eith...
Why do most people say that data services and the database are the most important parts of a system? From what I have seen, it is the front end development: GUI, WEBUI, XAML that is the most important. Certainly more important than the middle and database tiers. I don't think it is a big deal to build an application's database. After...
I want to make my fields as small as I can, but at the same time I don't want to have to go back and make them bigger later. I was wondering if there is some sort of quick reference for field sizes and/or types for common database fields. i.e., What is reasonable for an email field? 50? 100? Or is this not something I should worry abo...
I am very well versed in the theory and practice of relational database design. I know what works and what doesn't, what is performant and what is maintainable (almost - there's always place to tweak when you start having real data). It seems I can't find a substantial body of knowledge regarding distributed scalable databases such as ...
Our company has always struggled in terms of accurate and timely reporting. The primary reason is that our principal operational system has evolved over the past 20 years and is in an archaic flat file structure. We have had a few attempts at developing a Data Warehouse, but they have only been mildly successful, due mainly to the busi...
I apologise for the long-winded, rantyness of this but I'm up at 3am dreading having to go to work tomorrow and deal with this database... it just feels wrong but maybe I'm wrong and just like things done my way... Please tell me what you think. Our database schema looks something like: page: id, label, contentid, parentpageid content...
I have a MySQL(innodb) table 'items' with the following characteristics Large number of rows, and keeps on increasing. Large number of columns of various data-types including 'text'; primary key 'item_id' is present. There are additional requirements as follows: Need to query items based on their status Need to update status...
I've currently adding a bit of functionality that manages holiday lettings on top of a CMS that runs on PHP and MySQL. The CMS stores the property details on a couple of tables, and I'm adding a third table (letting_times) that will contain information about when people are staying at the property. Basic functionality would allow the us...
And what are the pitfalls to avoid? Are there any deal breaks for you? E.g., I've heard that exporting/importing the Cassandra data is very difficult, making me wonder if that's going to hinder syncing production data to development environment. BTW, it's very hard to find good tutorials on Cassandra, the only one I have http://arin.me/...
Hi all, I'm trying to create a system in Python in which one can select a number of rows from a set of tables, which are to be formatted in a user-defined way. Let's say the table a has a set of columns, some of which include a date or timestamp value. The user-defined format for each column should be stored in another table, and querie...
Short question: How should product categories that appear under multiple categories be managed? Is it a bad practice to do so at all? Background info: We have a product database with categories likes this: Products -Arts and Crafts Supplies -Glue -Paper Clips -Construction Paper -Office Supplies -Glue -Paper ...
Consider searching a table that contains Apartment Rental Information: A client using the interface selects a number of criteria that are represented as bit fields in the DB, for instance: AllowsPets HasParking HasDeck ModernKitchen etc.. We are facing a situation where each new client of our software has additional fields they w...