relational

What are the pros and cons of object databases?

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...

How Does Relational Theory Apply in Ways I can Care About while Learning it?

So I'm taking the Discrete Math course from MIT's OpenCourseWare and I'm wondering... I see the connection between relations and graphs but not enough to "own" it. I've implemented a simple state machine in SQL as well so I grok graphs pretty well, just not the more rigorous study of how relations and sets compeltely apply. Should I just...

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...

Relational camp and "real-world" database development

More than decade passed since first publication of Date's and Darwen's "The Third Manifesto" in 1995. What is the place of relational school of thought in today's database world? Is there any evidence that Manifesto's ideas changed mainstream software development and data management practices? Have they catalyzed creation of new data ma...

Choosing a database type

When would you use a bigtabe/simpledb database vs a Relational database? ...

How to wire a middle tier of Objects to a data tier consisting of a DataSet?

Howdy, I have a middle tier containing several related objects and a data tier that is using a DataSet with several DataTables and relationships. I want to call a Save method on one of my objects (a parent object) and have its private variable data transformed into a DataRow and added to a DataTable. Some of the private variable data ...

Recommendations for good book and/or online documentation for Entity Relationship Diagrams

Hi there, The wiki page on ERD has a lot of useful information, but I was wondering if you had any recommendations for good books and/or online documentation for a more in-depth description of ERD's? Thanks. ...

How do you document your database structure?

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...

What relational database innovations have there been in the last 10 years

The SQL implementation of relational databases has been around in their current form for something like 25 years (since System R and Ingres). Even the main (loosely adhered to) standard is ANSI-92 (although there were later updates) is a good 15 years old. What innovations can you think of with SQL based databases in the last ten years...

How to write a SQL statement which gets results via a relationship table? (many to many)

I have 3 tables (archive has many sections, section (may) belong to many archives): archive id PK description archive_to_section archive_id PK FK section_id PK FK section id PK description What would the SQL look like to list all the sections that belong a certain archive id? I am just learning SQL. From what I've read it s...

Modeling a 1 to 1..n relationship in the database

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? ...

What is Normalisation (or Normalization)? Why is it important?

Why do database guys go on about normalisation? What is it? How does it help? Is it even that important? Does it apply to anything outside of databases? ...

Real world MySQL/Postgres database schema examples and analysis tools

Hello, I am doing analysis on schema evolution and have really fallen short of real world database schemas that have evolved a bit and stored in version control. Mediawiki (http://svn.wikimedia.org/svnroot/mediawiki/trunk/phase3/maintenance/tables.sql) seems to be a very good candidate so far. I've considered Bugzilla, but they don't se...

Efficient persistent data structures for relational database

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's the best practice for primary keys in tables?

When designing tables, I've developed a habit of having one column that is unique and that I make the primary key. This is achieved in four ways depending on requirements: Identity integer column that auto increments. Unique identifier (GUID) A short character(x) or integer (or other relatively small numeric type) column that can serv...

Modeling Relational Entities Problem

This is something that made me doubt for a while so I thought it would be a good idea to post it here to find some insight, it's a relational database modeling problem/doubt I have the following problem: I have "questions" that must be in an specific "state", and all changes of state must be audited. I've found two solutions to this, ...

Should I delete or disable a row in a relational database?

In a brand new program where space isn't really that big a deal, is it better to delete a row or to disable a row by let's say a boolean "Disabled" and have the program just ignore it? For example, if I wanted to remove a user from a program. ...

How do you store a trie in a relational database?

I have a prefix trie. What is the recommended schema for representing this structure in a relational database? I need substring matching to remain efficient. ...

Is there a tool that can visually map table relationships in MySQL?

I'm looking for a tool that can map the relationships in my database in mysql. I would like something like the view that is generated in MS-SQL Server Management Studio in the 'Show Diagram Pane'. Does such a thing exist? I'm in charge of the design of the database and its using rails conventions. I would not be adverse to specifying...

[SQL] What's the best way to get related data from their ID's in a single query?

I have a table where each row has a few fields that have ID's that relate to some other data from some other tables. Let's say it's called people, and each person has the ID of a city, state and country. So there will be three more tables, cities, states and countries where each has an ID and a name. When I'm selecting a person, what'...