database-design

What is the best way to implement soft deletion?

Working on a project at the moment and we have to implement soft deletion for the majority of users (user roles). We decided to add an "is_deleted='0'" field on each table in the database and set it to '1' if particular user roles hit a delete button on a specific record. For future maintenance now, each SELECT query will need to ensure...

Mending bad BAD database design once data is in the system.

I know that that is not a question... erm anyway HERE is the question. I have inherited a database that has 1(one) table in that looks much like this. Its aim is to record what species are found in the various (200 odd) countries. ID Species Afghanistan Albania Algeria American Samoa Andorra Angola .... Western Sahara Yemen Zambia Zim...

Database Design Lookup tables.

I'm currently trying to improve the design of a legacy db and I have the following situation Currently I have a table SalesLead in which we store the the LeadSource. Create Table SalesLead( .... LeadSource varchar(20) .... ) The Lead Sources are helpfully stored in a table. Create Table LeadSource ( LeadSourceId int,...

What's wrong with foreign keys?

I remember hearing Joel Spolski mention in podcast 014 that he'd barely ever used a foreign key (if I remember correctly). However, to me they seem pretty vital to avoid duplication and subsequent data integrity problems throughout your database. Do people have some solid reasons as to why (to avoid a discussion in lines with Stack Over...

How do I enforce data integrity rules in my database?

I'm designing this collection of classes and abstract (MustInherit) classes… This is the database table where I'm going to store all this… As far as the Microsoft SQL Server database knows, those are all nullable ("Allow Nulls") columns. But really, that depends on the class stored there: LinkNode, HtmlPageNode, or CodePageNode. ...

How would I store a date that can be partial (i.e. just the year, maybe the month too) and output it later with the same specifity?

I want to let users specify a date that may or may not include a day and month (but will have at least the year.) The problem is when it is stored as a datetime in the DB; the missing day/month will be saved as default values and I'll lose the original format and meaning of the date. My idea was to store the real format in a column as a...

Separating user table from people table in a relational database

I've done many web apps where the first thing you do is make a user table with usernames, passwords, names, e-mails and all of the other usual flotsam. My current project presents a situation where non-users records need to function similarly to users, but do not need to the ability to be a first order user. Is it reasonable to creat...

What's the best way to store changes to database records that require approval before being visible?

I need to store user entered changes to a particular table, but not show those changes until they have been viewed and approved by an administrative user. While those changes are still in a pending state, I would still display the old version of the data. What would be the best way of storing these changes waiting for approval? I hav...

What are indexes and how can I use them to optimize queries in my database?

I am maintaining a pretty sizable application and database and am noticing some poor database performance in a few of our stored procedures. I always hear that "adding an index" can be done to help performance. I am certainly no DBA, and I do not understand what indexes are, why the help, and how to create them. I basically need an ind...

What columns generally make good indexes?

As a follow up to: http://stackoverflow.com/questions/105400/what-are-indexes-and-how-can-i-use-them-to-optimize-queries-in-my-database where I am attempting to learn about indexes, what columns are good index candidates?, specifically for an mssql database? After some googling, everything I have read suggests that columns that are gene...

MySQL: Advisable number of rows

Consider an indexed MySQL table with 7 columns, being constantly queried and written to. What is the advisable number of rows that this table should be allowed to contain before the performance would be improved by splitting the data off into other tables? ...

salient concerns and questions to consider in designing a website content management system

I'm designing my website and was curious, before I go rip someone else's ideas, what were the salient considerations and questions one should ask in designing a database? Thanks! ...

Conflicting desires in Database Design, with fields of two similar functions

Okay, so I'm making a table right now for "Box Items". Now, a Box Item, depending on what it's being used for/the status of the item, may end up being related to a "Shipping" box or a "Returns" box. A Box Item may be defective:if it is, a flag will be set in the Box Item's row (IsDefective), and the Box Item will be put in a "Returns" ...

Database schema for a hierarchial groups

I'm working on a database design for groups hierarchy used as the foundation of a larger system. Each group can contain other groups, and also 'devices' as leaf objects (nothing goes below device). The database being used is MS SQL 2005. (Though working in MS SQL 2000 would be a bonus; a solution requiring MS SQL 2008 is unfortunately n...

Visually designing a database structure

I am quite happy to code out tables by hand when making a database but it's not the easiest way to convey information about a database to someone else, especially someone that's not so comfortable coding the tables via a script and would instead use something such at phpMyAdmin. Is there thus a free program (for me to use it it'll have ...

Do you check your field- and table names against reserved word lists ?

I've sometimes had a problem with my field-, table-, view- oder stored procedure names. Example: SELECT from, to, rate FROM Table1 The Problem is that from is a reserved word in SQL-92. You could put the fieldname in double quotes to fix this, but what if some other db tools wants to read your database? It is your database design a...

Best Practices: Storing a workflow state of an item in a database?

I have a question about best practices regarding how one should approach storing complex workflow states for processing tasks in a database. I've been looking online to no avail, so I figured I'd ask the community what they thought was best. This question comes out of the same "BoxItem" example I gave in a prior question. This "BoxIte...

Performance implications of computed columns in SQL Server 2005 database?

The situation: we have a large database with a number of denormalized tables. We frequently have to resummarize the data to keep the summary tables in synch. We've talked on and off about using computed columns to keep the data fresh. We've also talked about triggers, but that's a separate discussion. In our summary tables, we denormali...

Decipher database schema

I've recently inherited the job of maintaining a database that wasn't designed very well and the designers aren't available to ask any questions. And I have a couple more coming my way in the near future. It's been tough trying to figure out the relationships between the tables without any kind of visual aid or database diagram. I wa...

Designing an 'Order' schema in which there are disparate product definition tables

This is a scenario I've seen in multiple places over the years; I'm wondering if anyone else has run across a better solution than I have... My company sells a relatively small number of products, however the products we sell are highly specialized (i.e. in order to select a given product, a significant number of details must be provide...