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...
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...
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,...
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...
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. ...
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...
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...
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...
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...
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...
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? ...
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! ...
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" ...
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...
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 ...
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...
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...
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...
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...
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...