Let's look at an example - books. A book can have 1..n authors. An author can have 1..m books. What is a good way to represent all of the authors of a book? I came up with an idea to create a Books table and an Authors table. The Authors table has a primary AuthorID key the author's name. The Books table has a primary Book ID and metada...
Just like the title says, using Microsoft SQL Server's database mirroring capability, can the mirroring occur between two clusters? All of the examples in MSDN show a topology indicating single servers for both the principal and mirror server. ...
We may start converting an old VS2003 MFC project to use the fancy new features provided by the MFC Feature Pack and VS2008. Several of the new UI controls would be very nice except for one thing - they automatically save their information to the registry. I don't have a problem with the registry, but for the multiple environments the us...
I know that primary keys based on Guids do not have the best performance (due to the fragmentation), but they are globally unique and allow replication scenarios. Integral identifiers, on the other side, have greater performance at the cost of scalability. But in what scenarios would someone want to use sequential uniqueidentifier as t...
I have a situation where I have two models, companies and permissions, where companies is in a separate database from my permissions database. This is a has and belongs to many relationship because each company can have many permissions and each permission can belong to many companies. The reason the two databases are split is because ...
RDBMS packages today offer a tremendous amount of functionality beyond standard data storage and retrieval. SQL Server for example can send emails, expose web service methods, and execute CLR code amongst other capabilities. However, I have always tried to limit the amount of processing my database server does to just data storage and ...
I wonder if this would be doable ? To insert an array into one field in the database. For instance I have a title, I want to have that title with only one id, but it's going to be bilingually used on the website. It feels a bit unnecessary to make another table to have their global ids and then another table with the actual titles link...
What are some useful Oracle optimizations one can use for an Application that mostly writes (updates) to an Oracle database? The general usage pattern here is not web-serving or logging, as is most cases, but instead to persist complex state of a system, so the only times reading is needed is when the system starts up, after that its u...
Note: Originally this question was asked for PostgreSQL, however, the answer applies to almost any database which has a JDBC driver that can detect foreign-key associations. Querying PostgreSQL data dictionary for foreign-keys and relationship between tables is very straightforward, but how can I use that information to generate a gr...
I've been profiling some queries in an application I'm working on, and I came across a query that was retrieving more rows than necessary, the result set being trimmed down in the application code. Changing a LEFT JOIN to an INNER JOIN trimmed the result set to just what was needed, and presumably would also be more performant (since le...
If I have a class representing access to one table in my database in a class:table relationship, so I can hide table details in one class. But as any useful application I have to query several tables. How can I accomodate this using the class:table design? ...
While designing a table my colleague here says that I should avoid identity column as it is specific to SQL Server and MS Access, But I differ with his views as it makes my coding simpler. Should I use identity column or not? If not what is best way to create the identity columns from application code? ...
Many database systems don't allow comments or descriptions of tables and fields, so how do you go about documenting the purpose of a table/field apart from the obvious of having good naming conventions? (Let's assume for now that "excellent" table and field names are not enough to document the full meaning of every table, field and rela...
I need a function which executes an INSERT statement on a database and returns the Auto_Increment primary key. I have the following C# code but, while the INSERT statement works fine (I can see the record in the database, the PK is generated correctly and rows == 1), the id value is always 0. Any ideas on what might be going wrong? ...
I have an UPDATE sql command that modifies a Date/Time field in a particular table. This table has two keys, ProductionCode and TestTime, which is what is being modified. The Production Code never changes for a particular record, but often a user will find that they entered the wrong test time and need to change it. I have the updated t...
This is my query: $query = $this->db->query(' SELECT archives.id, archives.signature, type_of_source.description, media_type.description, origin.description FROM archives, type_of_source, media_type, origin WHERE archives.type_of_source_id = type_of_source.id ...
How would you store formatted blocks of text (line breaks, tabs, lists - etc.) in a database (nothing specific) to be displayed on the web (XHTML) while maintaining a level of abstraction so that the data can be used in other applications or if the structure of the website were to change in the future? ...
So for this one project, we have a bunch of queries that are executed on a regular basis (every minute or so. I used the "Analyze Query in Database Engine " to check on them. They are pretty simple: select * from tablex where processed='0' There is an index on processed, and each query should return <1000 rows on a table with 1MM reco...
What are some good resources for grokking the uses and theories behind DataMining? ...
Is there any way in the SQL language or in MySQL (or other DBMA) to transfer a value from one cell to another? For example, say there is a table called user_cars with the following structure: |id| |user_name| |num_cars| Bob has 5 cars, and John has 3 cars. Is there any way to in one query subtract 2 cars from Bob and add 2 to John? I ...