How would you model booked hotel room to guests relationship (in PostgreSQL, if it matters)? A room can have several guests, but at least one. Sure, one can relate guests to bookings with a foreign key booking_id. But how do you enforce on the DBMS level that a room must have at least one guest? May be it's just impossible? ...
For example if I have an auto-numbered field, I add new records without specifying this field and let DB engine to pick it for me. So, will it pick the number of the deleted record? If yes, when? // SQL Server, MySQL. // Follow-up question: What happens when DB engine runs out of numbers to use for primary keys? ...
What is the best way to setup your pool with respect to:- When do you create connections? When do you close connections, and would you close all of them? Do you test connections are still good. When and how? How do you figure out a good number for the maximum number of connections? What sort of monitoring do you have in place to ensur...
So I am wondering if there is a definitive answer to this question. Also, does it matter if the index is clustered vs. non-clustered. Is it the same in all RDBMS implementations or is the exact behavior going to be proprietary? ...
Does anyone know of a Java library that provides a useful abstraction for analyzing and manipulating arbitrary relational database schemata? I'm thinking of something that could do things like LibraryClass dbLib = ...; DbSchema schema = dbLib.getSchema("my_schema"); List<DbTable> tables = schema.getTables(); and DbTable myTable = .....
Does anyone have any experience migrating from one DBMS to another? If you have done this, why did you do it? Features? Cost? Corporate Directive? At times, I've worked with DBAs who insisted that we not use features specific to a DBMS (for example, CLR Stored Procedures in SQL Server.) The DBAs point is, if we use these features, it ...
When would a database design be described as overnormalized? Is this characterization an absolute one? Or is it dependent on the way it is used in the application? Thanks. ...
I'm looking into using Lucene and/or Solr to provide search in an RDBMS-powered web application. Unfortunately for me, all the documentation I've skimmed deals with how to get the data out of the index; I'm more concerned with how to build a useful index. Are there any "best practices" for doing this? ...
I'm looking for material on persistent data structures that can be used to implement a relational model. Persistence in the meaning of immutable data structures. Anyone know of some good resources, books, papers and such? (I already have the book Purely Functional Data Structures, which is a good example of what I'm looking for.) ...
What is the most efficient way to detect duplicates in a 10 column / 50K row table? I'm using MSSQL 8.0 ...
I'm wondering if this is a good design. I have a number of tables that require address information (e.g. street, post code/zip, country, fax, email). Sometimes the same address will be repeated multiple times. For example, an address may be stored against a supplier, and then on each purchase order sent to them. The supplier may then...
Is there any Database Abstraction Layer (DAL) or Object Relational Mapper (ORM) that works on Google App Engine (GAE), and on normal relational databases (RDBS), other than web2py's? If not, is anybody working on porting one of the existing DAL/ORM to GAE? ...
I recently switched a project from MySQL InnoDB to PostgreSQL, and I feel bigger lags when inserting and updating data with ajax. This may be subjective. I know the Django devs recommend postgres and I know psycopg2 is supposed to be faster than MysqlDB. Personaly I like the way postgres enforces database integrity, but am mostly worried...
How do I track or get notified whenever a record is inserted or updated in a DB? I would like to notify an external application of the changes in near real time whenever such changes in DB occur. Are there DBMS independent and application programming language independent ways of doing this? If not, then is it possible with MS Access and ...
Following up this question: "Database enums - pros and cons", I'd like to know which database systems support enumeration data types, and a bit of detail on how they do it (e.g. what is stored internally, what are the limits, query syntax implications, indexing implications, ...). Discussion of use cases or the pros and cons should take...
We are embarking on a grand/huge project which will take us around 1 full year of coding (by 4 developers) to get off the launch pad. It is a complete Business Management System. We can use any commercially available RDBMS like Oracle, MS SQL Server, etc. but we are looking out to use freeware and open source RDBMS system like FireBird...
I'm having difficulties to answer this question. Can someone help me? Discuss the benefits of MYSQL and explain why it is gaining acceptance as the RDBMS of choice for many organizations worldwide. ...
Hi all suppose that I have this RDBM table (Entity-attribute-value_model): col1: entityID col2: attributeName col3: value and I want to use HBASe sue to scaling issues. I know that the only way to access Hbase table is using a primary key (cursor). you can get a cursor for a specific key, and iterate the rows one-by-one . The issue...
Hi again, I just finished transferring as much link-structure data concerning wikipedia (English) as I could. Basically, I downloaded a bunch of SQL dumps from wikipedia's latest dump repository. Since I am using PostgreSQL instead of MySQL, I decided to load all these dumps into my db using pipeline shell commands. Anyway, one of thes...
Hi again, I am trying to execute this SQL command: SELECT page.page_namespace, pagelinks.pl_namespace, COUNT(*) FROM page, pagelinks WHERE (page.page_namespace <=3 OR page.page_namespace = 12 OR page.page_namespace = 13 ) AND (pagelinks.pl_namespace <=3 OR pagelinks.pl_namespace ...