What are the options for storing hierarchical data in a relational database? Good Overviews Generally speaking you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually you end up with a combination of the options below that best fit your needs. The following provides some in depth reading: One more Nested Intervals vs. Adjacency List comparison: the best comparison of Adjacency List, Materialized Path, Nested Set and Nested Interval I've found. Models for hierarchical data: slides

How do I connect to a MySQL Database in Python? How do I connect to a MySQL database using a python program? Most Best Answer: Connecting to MYSQL with Python in 3 steps 1 - Setting You must install a MySQL driver before doing anything. Unlike PHP, only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it's hard to install it using easy_install. For Windows user, you can get an exe of MySQLdb. For Linux,

Why shouldn't I use mysql_* functions in PHP? What are the technical reasons for why one shouldn't use mysql_* functions? (e.g. mysql_query(), mysql_connect() or mysql_real_escape_string())? Why should I use something else even if they work on my site? Answer: The MySQL extension: Is not under active development Is officially deprecated as of PHP 5.5 (released June 2013). Has been removed entirely as of PHP 7.0 (released December 2015) Lacks an OO interface Doesn't support: Non-blocking, asynchronous queries Prepared statements or parameterized queries Stored procedures Multiple Statements Transactions The "new" password authentication method (on by default in MySQL 5.6; required in

How to list the tables in an SQLite database file that was opened with ATTACH? What SQL can be used to list the tables, and the rows within those tables in a SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool? Answer: The .tables, and .schema "helper" functions don't look into ATTACHed databases: they just query the SQLITE_MASTER table for the "main" database. Consequently, if you used ATTACH some_file.db AS my_db; then you need to do SELECT name FROM

Insert into … values ( SELECT … FROM … ) I am trying to insert into a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (MySQL, Oracle, SQL Server, Informix, and DB2). Is there a silver-bullet syntax coming from an SQL standard (for example, SQL-92) that would allow me to insert the values without worrying about the underlying database? Answer: Try: INSERT INTO

SQL Case Statement Syntax? What is the complete and correct syntax for the SQL Case statement? Solution : The complete syntax depends on the database engine you're working with: For SQL Server: CASE case-expression WHEN when-expression-1 THEN value-1 [ WHEN when-expression-n THEN value-n ... ] [ ELSE else-value ] END or: CASE WHEN boolean-when-expression-1 THEN value-1 [ WHEN boolean-when-expression-n THEN value-n ... ] [ ELSE else-value ] END expressions, etc: case-expression - something that produces a value when-expression-x - something that is compared against the case-expression value-1 -

Inheritance in database? Is there any way to use inheritance in database (Specifically in SQL Server 2005)? Suppose I have few field like CreatedOn, CreatedBy which I want to add on all of my entities. I looking for an alternative way instead of adding these fields to every table. Solution : There is no such thing as inheritance between tables in SQL Server 2005, and as noted by the others, you can get as far as getting help adding the necessary columns to the tables when

Is there a version control system for database structure changes? I often run into the following problem. I work on some changes to a project that require new tables or columns in the database. I make the database modifications and continue my work. Usually, I remember to write down the changes so that they can be replicated on the live system. However, I don't always remember what I've changed and I don't always remember to write it down. So, I make a push

Check for changes to an SQL Server table? How can I monitor an SQL Server database for changes to a table without using triggers or modifying the structure of the database in any way? My preferred programming environment is .NET and C#. I'd like to be able to support any SQL Server 2000 SP4 or newer. My application is a bolt-on data visualization for another company's product. Our customer base is in the thousands, so I don't want to have to put in requirements that we modify