I'm currently developing a monthly checklist system for our organization. A user may login, select a month, then submit a list of yes/no questions relevant to that month for our organization's purposes. Some of the questions are used in more than 1 month's checklist, so I'm creating an intersection table to facilitate this one-to-many re...
What are the constituents of a good relational database design? I'd want to ask this question in a bit unusual way, lets see if anyone likes the idea. Also read on to see how it's not a duplicate of the question about DB design best practices (or any other question for that matter). The idea is that as a result of voting we will have co...
I'm trying to avoid reinventing the wheel when it comes to storing street addresses in a table only once. Uniqueness constraints won't work in some common situations: 100 W 5th Ave 100 West 5th Ave 100 W 5th 200 N 6th Ave Suite 405 200 N 6th Ave #405 I could implement some business logic or a trigger to normalize all fields before ...
This is a generic database design question - What are the benefits of using a synonym in database development, over a simple view? What are the main considerations to keep in mind when choosing between the two? ...
I am working on a php project that needs to store information about various buildings and will store different types of information depending on the type of the building: Class Building { var $location var $name } The Building class will be extended by classes like, House and Office so the classes will look like this (just an ...
Hi, I'm writing an online project asset tracker but I'm new to MySQL. What would be the best way of tracking projects, users, and assets for something like this? I have 3 tables for assets, users, and projects. Users should own projects and assets. Assets could be members of multiple projects, and projects should be able to be seen by mu...
I have many tables that use Lookup/Enum references for most of their column values. For example: Person Table - PersonID | RaceCode | HairColorCode | HairStyleCode | TeethConditionCode Location Table - LocationID | SizeCode | ExteriorColorCode | ConditionCode Things like Race,Size,Color,Condition,etc would just be foreign key referenc...
I would like some viewpoints on the best way to handle situations where you have normal (traditional) database data and at the same time also have data that is more like "ini file" data and you want to maintain integrity between them (heirachical data?). For example, suppose I have a "CURRENCY" table with 100 rows. At the same time I ha...
Someone asked the question: "INT, BIGINT or UUID/GUID in Oracle, DB2, Derby and HSQLDB?" and I started to think about all the database schemas I've designed and the books that I've read and not one reference gave any real clear advice about creating indexes. For example; if you have a compound index like date() ++ foo() ++ bar() Whil...
Option A We are working on a small project that requires a pricing wizard for custom tables. (yes, actual custom tables- the kind you eat at. From here out I'll call them kitchen tables so we don't get confused) I came up with a model where each kitchen table part was a database table. So the database looked like this: TableLineItem --...
Let's say we're having an application which should be able to store all kind of products. Each product has at least an ID and a Name but all other attributes can be defined by the user himself. E.g. He could create a productgroup Ipods which would contain attributes capacity and generation E.g. He could create a productgroup TShirts wi...
Is there a simple method to decide on what fields and indexes are needed for each table in an app you design? For example, if it is a webapp that simply lets people create lists (any number of lists, and users can create "things to do" list or "shopping" list), and the user can assign other users to edit the list, and whether the list i...
In my web app I have a chatroom. I wish to have each user's name to be colored differently rather than having them all black. What is the best way to achieve this? Should I store the RGB/HEX code for each user as they sign up to my app and put that string as a field as part of their record in the database? ...
What type of architecture, design and software would one need to provide something similar to the excellent custom report functionality provided by google analytics. To be more specific we want to user to be able to specify dimensions and metrics from a list and generate a report. Do we need a data warehouse? Do we need OLAP? Would ...
I want to record what various sources have to say about a historical figure. i.e. The website Wikipedia says that Susan B. Anthony was born February 15, 1820 and her favorite color was blue The book Century of Struggle says that Susan B. Anthony was born on February 12, 1820 and her favorite color was red The book History of Woman's S...
The software I'm working with has 2 tables, lead and customer. When we sell our product to someone, a record is created in the customer table with data from the lead table (as well as some additional data). Currently there is no relationship between the two tables. The best that exists now is the lead object has a function that will do ...
I am creating a simple design for a social-networking site using the MVC paradigm(in CakePHP) for a project, I have a table called Users which stores all the User Details, I have a Groups Table which stores all the Group details, the relation between these 2 models is has and belongs to many, then I have a group_portfolios table which st...
From first glance, it would appear I have two basic choices for storing ZIP codes in a database table: Text (probably most common), i.e. char(5) or varchar(9) to support +4 extension Numeric, i.e. 32-bit integer Both would satisfy the requirements of the data, if we assume that there are no international concerns. In the past we've ...
Yesterday, I asked this question regarding best practices for a simple information retrieval system I am beginning to work on. Today, my customer asked me if it is possible to allow them to add fields to the primary entity at a later date using the administration interface. That is, the application allows you to search across one databa...
I always get stuck on foreign keys so I have a question about where the foreign key should reside in a bug tracking system in which a bug has a single status at any given time while only a small number of statuses exist (Open, Under Investigation, Resolved, Pending Approval). So each status has many bugs associated with it. My assumption...