database-design

Extending SQL Server full-text index to search through foreign keys

I know that a SQL Server full text index can not index more than one table. But, I have relationships in tables that I would like to implement full text indexes on. Take the 3 tables below... Vehicle Veh_ID - int (Primary Key) FK_Atr_VehicleColor - int Veh_Make - nvarchar(20) Veh_Model - nvarchar(50) Veh_LicensePlate - nvarchar(10) At...

Versioning Database Persisted Objects, How would you?

(Not related to versioning the database schema) Applications that interfaces with databases often have domain objects that are composed with data from many tables. Suppose the application were to support versioning, in the sense of CVS, for these domain objects. For some arbitry domain object, how would you design a database schema to ...

What is the longest human name you can expect?

What is the longest name that you should expect to get as input to your program or database? I don't want to truncate unusual names, but I also don't want people to paste a novel in my name field as this could result in security problems. Has anybody ever been bitten by setting this field size too short? ...

Expressing an is-a relationship in a relational database

I was wondering if there is a clean way to represent an is-a relationship as illustrated by this example: This DB stores recording times for three types of programs: movies, game shows, drama. In an object oriented sense each of these is-a program. Each of these subclasses have different properties. Here are the tables (fk prefix indica...

An affordable tool for DB modeling

Hello! Could you guys recommend some affordable SQL modeling tool which supports SQL Server, PostgreSQL and MySQL? I'm looking into up to $300 per license range. One tool per answer, please! Thanks! ...

what's the best implemention of client creatable and modifiable web forms in a relational database?

In several web application projects I've been a part of, the client asks to be able to create their own forms. The question arises on how to store their form definitions, and then how to store user inputted values into those custom forms. I've seen it done two ways: Assuming that the client only defines how many fields, and what label...

Good database table design for storing localized versions of data

I'm trying to design some tables to store some data, which has to be converted to different languages later. Can anybody provide some "best practices" or guidelines for this? Thanks ...

What is an orthogonal index?

A table in my area of responsibility of our product has been criticised as having more than one orthogonal index. What is an orthogonal index? Why is it bad? How can the situation be avoided? --Update-- The back-end database engine isn't necessarily relevant here as our application is database-agnostic. But if it helps, Oracle is one p...

Can you have too many stored procedures?

Is there such a thing as too many stored procedures? I know there is not a limit to the number you can have but is this any performance or architectural reason not to create hundreds, thousands?? ...

What are the different types of indexes, what are the benefits of each?

What are the different types of indexes, what are the benefits of each? I heard of covering and clustered indexes, are there more? Where would you use them? ...

How do you optimize tables for specific queries?

What are the patterns you use to determine the frequent queries? How do you select the optimization factors? What are the types of changes one can make? ...

SQL Null set to Zero for adding

I have a SQL query (MS Access) and I need to add two columns, either of which may be null. For instance: SELECT Column1, Column2, Column3+Column4 AS [Added Values] FROM Table where Column3 or Column4 may be null. In this case, I want null to be considered zero (so 4 + null = 4, null + null = 0). Any suggestions as to how to accomplis...

Moving from ints to GUIDs as primary keys

I use several referenced tables with integer primary keys. Now I want to change ints to GUIDs leaving all references intact. What is the easiest way to do it? Thank you! Addition I do understand the process in general, so I need more detailed advices, for example, how to fill new GUID column. Using default value newid() is correct, bu...

Combining split date ranges in a SQL query

I'm working on a query that needs to have some data rows combined based on date ranges. These rows are duplicated in all the data values, except the date ranges are split. For example the table data may look like StudentID StartDate EndDate Field1 Field2 1 9/3/2007 10/20/2007 3 True 1 10/21/2007 6/12/2008 3 True 2 10/10...

What is important to keep in mind when designing a database?

What is important to keep in mind when designing a database? I don't want to limit your answer to my needs as I am sure that others can benefit from your insights as well. But I am planning a content management system for a multi-client community driven site. ...

How many database indexes is too many?

I'm working on a project with a rather large Oracle database (although my question applies equally well to other databases). We have a web interface which allows users to search on almost any possible combination of fields. To make these searches go fast, we're adding indexes to the fields and combinations of fields on which we believe...

Subqueries vs joins

I refactored a slow section of an application we inherited from another company to use an inner join instead of a subquery like where id in (select id from ... ) The refactored query runs about 100x faster. (~50 seconds to ~0.3) I expected an improvement, but can anyone explain why it was so drastic? The columns used in the where clau...

Database structure to track change history

I'm working on database designs for a project management system as personal project and I've hit a snag. I want to implement a ticket system and I want the tickets to look like the tickets in Trac. What structure would I use to replicate this system? (I have not had any success installing trac on any of my systems so I really can't see ...

cloning hierarchical data

Hi, let's assume i have a self referencing hierarchical table build the classical way like this one: CREATE TABLE test (name text,id serial primary key,parent_id integer references test) insert into test (name,id,parent_id) values ('root1',1,NULL),('root2',2,NULL),('root1sub1',3,1),('root1sub2',4,1),('root 2sub1',5,2),('root2sub2',6,2...

varchar(255) v tinyblob v tinytext

My side question is there really any difference between tinyblob & tinytext? Buy my real question is what reason, if any, would I choose varchar(255) over tinyblob or tinytext? ...