database-design

Database Design

I need to create a database to store simulation results. What resources are out there to help me design this? EDIT: For those who want more details... We work with a discrete event simulation used by the military. Important simulation events are thus when weapons are fired, when kills occur, and also when weapons fail to kill a targe...

SQL Server normalization tactic: varchar vs int Identity

I'm just wondering what the optimal solution is here. Say I have a normalized database. The primary key of the whole system is a varchar. What I'm wondering is should I relate this varchar to an int for normalization or leave it? It's simpler to leave as a varchar, but it might be more optimal For instance I can have People =========...

How to store directory / hierarchy / tree structure in the database?

How do i store a directory / hierarchy / tree structure in the database? Namely MSSQL Server. @olavk: Doesn't look like you've seen my own answer. The way i use is way better than recursive queries :) p.p.s. This is the way to go! ...

Can you recommend a PostgreSQL Visual Database Designer for Linux?

When I'm in Windows, I use the excellent MicroOLAP Database Designer for PostgreSQL, but its not open source or multiplataform. Do you know or can recommend me an alternative to this software, that I can use in Linux? EDIT: Just to clarify, I don't want to use wine to emulate MicroOlap for PostgreSQL, it doesn't work too well, I would p...

Relational Database Design Patterns?

Design patterns are usually related to object oriented design. Are there design patterns for creating and programming relational databases? Many problems surely must have reusable solutions. Examples would include patterns for table design, stored procedures, triggers, etc... Is there an online repository of such patterns, similar to m...

Sql design problem - items in multiple sections

I've got a sections table, and an items table. The problem is each item may be in one or more sections, so a simple 'section_id' for each item won't work, and sql doesn't have a way to store arrays where I can do say "WHERE 5 in section_ids"... I've considered storing the list of ids as a comma separated string, the problem is I see no...

Can I have non-measure codes mixed with measures in my fact table?

We're doing a complex bit of data accumulation. Our customer sends us some stuff that includes two dimensions (time and a business unit). Time is mostly year-month. The business unit dimension has just a few attributes: a name, and a few categories to which BU's can belong for reporting and analysis purposes. The stuff they send us i...

CouchDB modeling for multi-user

I am already excited about document databases and especially about CouchDB's simplicity. But I have a hard time understanding if such databases are a viable option for multi user systems. Since those systems require some kind of relations between records which document databases do not provide. Is it completely the wrong tool for such c...

Best Way to Manage Configuration Data

I'm working on a SaaS application where each customer will have different configurations depending on the edition they have purchased, additional features they have purchased, etc. For example, a customer might have a limit of 3 custom reports. Obviously I want to store this configuration in the database, but I am unsure of the best app...

How to design a fact table for delivery data

I'm building a data warehouse that includes delivery information for restaurants. The data is stored in SQL Server 2005 and is then put into a SQL Server Analysis Services 2005 cube. The Deliveries information consists of the following tables: FactDeliveres BranchKey DeliveryDateKey ProductKey InvoiceNumber (DD: degenerate dimension)...

What tools are available for TDDD (Test Driven Database Development)?

About a year ago, I picked up Scott Ambler's Refactoring Databases: Evolutionary Database Design. I was won over to the idea that just as you can develop your code with TDD, you probably should be covering your databases with unit tests (at least) or even writing database tests before you make a change to the schema so you do database wo...

Good database design for recall and comparison of 2D data arrays?

I am looking to store 2D arrays of 900x100 elements in a database. Efficient recall and comparison of the arrays is important. I could use a table with a schema like [A, x, y, A(x,y)] such that a single array would compromise 90,000 records. This seems like an ~ok~ table design to store the array, and would provide for efficient recal...

SQL schema to hold history of employee actions when employees come/go/get promoted, etc

I'm writing an app that contains the following tables: (1) employee_type, (2) employee and (3) employee_action. Employee_action is foreign-keyed to employee, and contains a description of what happened and the date of the event, just as you might expect. However, employees can change their type over time (promotion, demotion, relocatio...

Database schema to track random settings in website for marketing

I have Flex based consumer website where I would like to change various look and feel type settings based on random and other criteria, and then track these through to what results in the most sales. For instance I might completely switch out the homepage, show different things depending upon where people come from. I might show or hid...

Primary Key versus Unique Constraint?

Hi! I'm currently designing a brand new database. In school, we always learned to put a primary key in each table. I read a lot of articles/discussions/newsgroups posts saying that it's better to use unique constraint (aka unique index for some db) instead of PK. What your point of view? ...

Composite primary keys versus unique object ID field

I inherited a database built with the idea that composite keys are much more ideal than using a unique object ID field and that when building a database, a single unique ID should never be used as a primary key. Because I was building a Rails from end for this database, I ran into difficulties getting it to conform to the Rails conventi...

What is the ideal data type to use when storing latitude / longitudes in a MySQL database?

Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database? ...

One To Many To Itself

How would one structure a table for an entity that can have a one to many relationship to itself? Specifically, I'm working on an app to track animal breeding. Each animal has an ID; it's also got a sire ID and a dame ID. So it's possible to have a one to many from the sire or dame to its offspring. I would be inclined to something l...

Many-to-Many with "Primary"

I'm working on a database that needs to represent computers and their users. Each computer can have multiple users and each user can be associated with multiple computers, so it's a classic many-to-many relationship. However, there also needs to be a concept of a "primary" user. I have to be able to join against the primary user to lis...

Database Design Issues with relationships

I'm working on an upgrade for an existing database that was designed without any of the code to implement the design being considered. Now I've hit a brick wall in terms of implementing the database design in code. I'm certain whether its a problem with the design of the database or if I'm simply not seeing the correct solution on how to...