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...
(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 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? ...
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...
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! ...
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...
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 ...
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...
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? I heard of covering and clustered indexes, are there more? Where would you use them? ...
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? ...
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...
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...
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? 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. ...
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...
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...
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 ...
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...
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? ...