I am intentionally leaving this quite vague at first. I'm looking for discussion and what issues are important more than I'm looking for hard answers. I'm in the middle of designing an app that does something like portfolio management. The design I have so far is Problem: a problem that needs to be solved Solution: a proposed soluti...
I've grown up as a programmer using MySQL all the way. Recently, I started changing some of our applications to support MS SQL Server as an alternative backend. One of the compatibility issues I ran into is the use of MySQL's CREATE TEMPORARY TABLE to create in-memory tables that hold data for very fast access during a session with no ne...
what database system should a beginner in c language to use? Can i use MySQL? Thanks ...
The source database is quite large. The target database, doesn't grow automatically. They are on different machines. I'm coming from a MSSql, MySQl background and IDS11 seems overly complex (I am sure, with good reason) ...
When setting up foreign keys in SQL Server, under what circumstances should you have it cascade on delete or update, and what is the reasoning behind it? This probably applies to other databases as well. I'm looking most of all for concrete examples of each scenario, preferably from someone who has used them successfully. ...
There is a lot of information out there on object-relational mappers and how to best avoid impedance mismatch, all of which seem to be moot points if one were to use an object database. My question is why isn't this used more frequently? Is it because of performance reasons or because object databases cause your data to become propriet...
I've done many web apps where the first thing you do is make a user table with usernames, passwords, names, e-mails and all of the other usual flotsam. My current project presents a situation where non-users records need to function similarly to users, but do not need to the ability to be a first order user. Is it reasonable to creat...
I've worked on a variety of systems as a programmer, some with Oracle, some with MySQL. I keep hearing people say that Oracle is more stable, more robust, and more secure. Is this the case? If so in what ways and why? For the purposes of this question, consider a small-medium sized production DB, perhaps 500,000 records or so. ...
We all have our favourite database. If you look objectively at your chosen database, what drawbacks does it have and what could be improved? The rules: One reply per drawback with; a short description of the limitation, followed by; a more detailed description, an explanation of how it could be done better or an example of another tec...
Should I use a hand-written schema for my projected developed in a high-level language (such as Python, Ruby) or should I let my ORM solution auto-generate it? Eventually I will need to migrate without destroying all the data. It's okay to be tied to a specific RDBMS but it would be nice if features such as constraints and procedures cou...
Design patterns are usually related to object oriented design. Are there design patterns for creating and programming relational databases? Many problems surely must have reusable solutions. Examples would include patterns for table design, stored procedures, triggers, etc... Is there an online repository of such patterns, similar to m...
In Ruby-on-Rails, this is called a "polymorphic association." I have several Commentable things in my application, the tables for each are below: Post id | title | text | author (FK:Person.id) | ... Person id | name | ... Photo id | title | owner (FK:Person.id) | path | ... I'd like to add a Comments table as follows: Comments id ...
That's the question. Give only one reason you think why have OODB failed or why many systems nowadays still use relational databases. ...
A few questions on MS Access databases - Size: Are there limits to the size of an access database? The reason i ask is that we have an access database that has a few simple tables. The size of the db is about 1GB. When I do a query on it, i see it taking over 10 minutes to run. With proper indexing, should MS Access be able to hand...
Modern RDBMS's have support for XML column types and functionality for dealing with XML in stored procedures. Historically I would always have mapped hierarchical data (whether of OO objects or XML) to relational tables. Given the widespread database support for XML should I change my ways? ...
I have around 3500 flood control facilities that I would like to represent as a network to determine flow paths (essentially a directed graph). I'm currently using SqlServer and a CTE to recursively examine all the nodes and their upstream components and this works as long as the upstream path doesn't fork alot. However, some queries tak...
Object-relational mapping has been well discussed, including on here. I have experience with a few approaches and the pitfalls and compromises. True resolution seems like it requires changes to the OO or relational models themselves. If using a functional language, does the same problem present itself? It seems to me that these too p...
SQL databases seem to be the cornerstone of most software. However, it seems optimized for textual data. In fact when doing any queries involving numerical data, integers specifically, it seems inefficient that the numbers are getting converted to text and then back to native formats both ways between the application and the database. Th...
Hi, I'm about to enter the 1st phases of designing a commodities trading application. A major function of this application is the capture and evaluation of pricing information. Pricing in this market is not fixed. It is usually a market benchmark +/- a premium. For example: (A and B are market benchmarks) Price = A + $1 Price = A + .5...
In cleaning up this answer I learnt a bit about TRIGGERs and stored procedures in MySQL, but was stunned that, while BEFORE INSERT and BEFORE UPDATE triggers could modify data, they seemingly couldn't cause the insert/update to fail (ie. validation). In this particular case I was able to get this to work by manipulating the data in such ...