database-design

Scalable Database System, Critique requested.

I'm looking to build a scalable database solution for the back end of my website. I've been reading about database design lately, and I seem to have developed an idea on my own that might work. I think this is a novel way of maintaining n databases with synchronized data, but I could be wrong. So I'm asking SO to evaluate the idea and te...

Joining with subqueries, counting and grouping.

Hello everyone, I have 3 tables, which are each 1:n. an entry in table1 has n entries in table2, and so on. lets call them cars, wheels, and screws for illustration. Screws can be clean(1) or rusty(2) I am joining them together, because I want to count 2 things. First, I want to have rows telling me how many good/bad screws per whee...

Too many fields on my table but want to add more...

I have a table in my SQLServer 2000 database that has in my opinion been poorly designed. It has a large number of fields in it and needs refactoring. However until I can do this I have other projects going on and one them requires a relationship with this table. I am really reticent to add a further field to this table at this time. (...

Database naming, static, incoming and analyzed data

We are running a database system which is basically working as a pipeline flow architecture, i.e. we have lot of incoming data, process this using a variety of in-house knowledge data, and then we produce output data. Right now everything is intermingled, which makes it difficult to know what is autogenerated and what is not etc. We ar...

Diagram to model database entries

Hi all! Do you know a diagram to show the entries of an database? I guess something like the object diagram in UML. I know there is the entity-relationship model but it does not show the existing entries of the database. ...

Help with db schema?

I am creating a simple blog app and so far I have 3 tables, Posts, Comments, and Authors. I will just list the primary key for each table as well as a foreign key. Posts will contain postid as a primary key. Comments will contain commentid as a primary key and postid as a foreign key. Posts has a 0 to many relationship with comments....

What column should the clustered index be put on?

Lately, I have been doing some reading on indexes of all types and the main advice is to put the clustered index on the primary key of the table, but what if the primary key actually is not used in a query (via a select or join) and is just put for purely relational purposes, so in this case it is not queried against. Example, say I hav...

Keys, Lists, Values

I think I've worked myself into a pretty stupid corner, here. Let me show you the schema. I've got a lot of things around it already but I've suddenly realized a big problem. Units - UnitId Rings - RingId Keys - RingId (FK) - KeyId (PK) - KeyLiteral (FK) - KeyValue Literals - LiteralId - LiteralValue It was good for a while. Basica...

IDs for Information on More Than One DB/Server

I'm working on a project that I want to have be as flexible and scalable as possible from the beginning. A problem I'm concerned about is one best described by Joshua Schacter in Founders at Work, who noted it as one detail he wish he would've planned for ahead of time. Scaling past one machine, one database, is very challenging, eve...

How to update multiple information at different locations(table) on change of a record?

This is not SO Meta question. I am using SO only as example. In StackoverFlow each answer, each comment, each question, each vote has a effect which produces a badge at some point of time. I mean after every action a list of queries are tested. E.g. If Mr.A up votes Mr.B Answer. So we have to check is this Mr.B's answer upvoted 100 ti...

Design a database

Hi, I want the best possible solution for the below stated problem. I am using SQL Server 2005 and asp.net 2.0. I have a master table where all the test scripts of different projects are stored. From the test scripts in master table i need to create a module and add the required test scripts to the module. UI design is: Create New Mod...

How to best represent addresses in a database

Possible Duplicates: Is there common street addresses database design for all addresses of the world? What is the best way to store international addresses in a database? Best practices for consistent and comprehensive address storage in a database I currently have four tables, Customers, Contacts, Facilities and Clients. E...

Positive or negative boolean field names

A table's boolean fields can be named using the positive vs the negative... for example, calling a field: "ACTIVE" , 1=on / 0=off or "INACTIVE" , 0=on / 1=off Question: Is there a proper way to make this type of table design decision, or is it arbitrary? My specific example is a messages table with a bool field (private/public)...

model an abstract base class and subclasses in a database

I have 4 subclasses: Video, Image, Note, and Form. Each one contains different types of data. For example, the Image class contains a path to the image file on disk and image properties, and the Form class contains the form field values. The common element between each item, however, is the GPS coordinates and heading, so I have the foll...

C# Factory Pattern

I am building a search application that has indexed several different data sources. When a query is performed against the search engine index, each search result specifies which data source it came from. I have built a factory pattern that I used to display a different template for each type of search result, but I've realized that thi...

cascading deletes causing multiple cascade paths

I am using SQlServer 2008, and an extract of some datatables is displayed below: Users Id (PK) UserItems UserId (PK) ItemId (PK) - (Compound key of 2 columns) ... UserItemVotes UserId (PK) ItemId (PK) VoterId (PK) - (Compound key of 3 columns) I have the following relationships defined: User.Id -> UserItems.UserId (UserItems.Use...

location based application

Hi all. I am building an application that that will allow people to post information in different areas of a city (for about 10 cities). Do you guys know of any existing (normalized) databases/datasets that have this kind of information? Or would I need to create one from scratch? Any pointers suggestions very much appreciated! ...

Database design question

I'm currently developing a website for a hotel. And one of the things I'm about to implement is worker->superior relationship. What is the best way to do so in MySQL? Here is what I mean: a chef's superior is a head chef, head chef's superior is shift manager, shift manager's superior is general manager. In the employee table, I could m...

implementing UNIQUE across linked tables in MySQL

a USER is a PERSON and a PERSON has a COMPANY - user -> person is one-to-one, person -> company is many-to-one. person_id is FK in USER table. company_id is FK in PERSON table. A PERSON may not be a USER, but a USER is always a PERSON. If company_id was in user table, I could create a unique key based on username and company_id, but i...

MSSQL2008: Bit arrays and bit-wise operations + database Brainstorm

Hi all. I have a coding problem where I have to store a 'note' and the dates the note applies to. Think of: Note 1 Hi, today Mr Client rang arranging these appointments. 30/8/2009, 31/8/2009, 5/9/2009 Note 2 Business as usual. 30/8/2009 Note 3 Restaurant is shut. 6/9/2009 I need to store the following data in a database, while maint...