database-design

How do you store a trie in a relational database?

by

How about the Materialized Path design? CREATE TABLE trie ( path VARCHAR(<maxdepth>) PRIMARY KEY, …other attributes of a tree node… ); To store a word like “stackoverflow”: INSERT INTO trie (path) VALUES (‘s’), (‘st’), (‘sta’), (‘stac’), (‘stack’), (‘stacko’), (‘stackov’), (‘stackove’), (‘stackover’), (‘stackover’), (‘stackoverf’), (‘stackoverflo’), (‘stackoverflow’); The materialized path in the tree is the prefixed sequence … Read more

Best practice – logging events (general) and changes (database)

by

Logging database changes as far as inserts/deletes/updates, as far as best practices go, is usually done by a trigger on the main table writing entries into a audit table (one audit table per real table, with identical columsn + when/what/who columns). The list of events as a generic list doesn’t exist. It’s really a function … Read more

Table with 80 million records and adding an index takes more than 18 hours (or forever)! Now what?

by

Ok turns out that this problem was more than just a simple create a table, index it and forget problem 🙂 Here’s what I did just in case someone else faces the same problem (I have used an example of IP Address but it works for other data types too): Problem: Your table has millions … Read more

designing database to hold different metadata information

by

This is called the Observation Pattern. Three objects, for the example Book Title=”Gone with the Wind” Author=”Margaret Mitchell” ISBN = ‘978-1416548898’ Cat Name=”Phoebe” Color=”Gray” TailLength = 9 ‘inch’ Beer Bottle Volume = 500 ‘ml’ Color=”Green” This is how tables may look like: Entity EntityID Name Description 1 ‘Book’ ‘To read’ 2 ‘Cat’ ‘Fury cat’ 3 … Read more

Is the usage of stored procedures a bad practice?

by

Stored procedures have been falling out of favour for several years now. The preferred approach these days for accessing a relational database is via an O/R mapper such as NHibernate or Entity Framework. Stored procedures require much more work to develop and maintain. For each table, you have to write out individual stored procedures to … Read more