How does you build a database?

An article on the Reddit that explains the implementation of the database is very interesting to record here.

Respondents Technical_guy:

Its a great question, and deserves a long answer.

Most database servers is built in C, and store data using b-tree type constructs. In the old days there were a product called C-isam (C Library for an indexed sequential access method) which are a low level Library to help C programmers write data in b-tree format. So-need to know about btrees and understand what these is.

Most databases store data separate to indexes. Lets assume a record (or row) is an bytes long and you write 5 rows of the data to a file. If The row contains columns such as first name, last name, address etc. and you want to search for a specific record by LA St Name, you can open the file and sequentially search through each record but this is very slow. Instead you open a index file which just contains the LastName and the position of the record in the data file. Then, if you have the position open the data file, Lseek to that position and read the data. Because index data is very small it was much quicker to search through index files. Also as the index files is stored in btrees on it very quick to effectively does a quicksearch (divide and conquer) to find The record is looking for.

So, understand for one "table", you would have a data file with the data and one (or many) index files. The first index file could is for LastName, the next could is to search by SS number etc. When the user defines their query to get some data, they decide which index file to search through. If you can find all info on C-isam (there used to is an open source version (or cheap commercial) called D-isam) Understand this concept quite well.

Once You has stored data and has the index files, using an ISAM type approach allows-GET a record based on a value , or PUT a new record. However modern database servers all support SQL, so you need an SQL parser that translates the SQL statement into a Sequen Ce of related GETs. SQL may join 2 tables A optimizer is also needed to decide which table to read first (normally based on number of rows In each table and indexes available) and how to relate it to the next table. SQL can INSERT data so do need to parse this into PUT statements but it can also combine multiple inserts into Transactio NS so need a transaction manager to control this, and you'll need transaction logs to store wip/completed Transactio NS.

It is possible you'll need some backup/restore commands to backup your data files and index files and maybe also your TR Ansaction log files, and if you really want to go for it you could write some replication tools to read your transaction l OG and replicate the transactions to a backup database on a different server. Note If you want your client programs (for example a SQL UI like phpMyAdmin) to reside on separate machine than your data Base server you'll need to write a connection manager this sends the SQL requests over TCP/IP to your server and then authe Nticate it using some credentials, parse the request, run your GETS and send the data to the client.

So these database servers can is a lot of work, especially for one person. But can I create simple versions of these tools one at a time. Start with what to store data and indexes, and what to retrieve the data using an ISAM type interface.

There is books out There-look for older books on MySQL and msql, look for anything on Google re btrees and ISAM, look F or open source C libraries this already do ISAM. Get a good understanding on file IO to a Linux machine using C. Many commercial databases now dont even use the filesystem for their data files because of cacheing Issues-they write di rectly to Raw disk. You want to just write to files initially.

I hope this helps a little bit.

Respondents Rbobby:

Good Lord ... what's an open ended question.

There ' s tons of literature around what makes a "good" database (normal forms). A lot of it might do sense immediately. Http://en.wikipedia.org/wiki/Normal_forms

There ' s tons of books/guides about what to create a database model (tables, columns, relationships, etc). Http://en.wikipedi A.org/wiki/data_modeling

There ' s tons of books/guides for what to implement a database model using a specific vendor ' s database engine (eg. how to C reate a table in SQL Server or Oracle or vendor_du_jour). http://www.databasejournal.com/features/mssql/article.php/ 3759371/create-your-first-sql-server-database-in-3-quick-steps.htm

Maybe start with a "dummies" book. They generally give a reasonable starting point ... you'll be better equipped to google/ask questions after having read one Of these. Http://www.dummies.com/how-to/content/knowing-just-enough-about-relational-databases.html

Note: Here are only excerpts from the top two votes higher answers, if you are interested, you can directly see the original text.

Reference:

https://www.reddit.com/r/Database/comments/27u6dy/how_do_you_build_a_database/

How does you build a database?