database-design

Database Structure for Tree Data Structure [closed]

by

You mention the most commonly implemented, which is Adjacency List: https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/25/hierarchies-convert-adjacency-list-to-nested-sets There are other models as well, including materialized path and nested sets: http://communities.bmc.com/communities/docs/DOC-9902 Joe Celko has written a book on this subject, which is a good reference from a general SQL perspective (it is mentioned in the nested set article link above). Also, Itzik … Read more

How to design a product table for many kinds of product where each product has many parameters

by

You have at least these five options for modeling the type hierarchy you describe: Single Table Inheritance: one table for all Product types, with enough columns to store all attributes of all types. This means a lot of columns, most of which are NULL on any given row. Class Table Inheritance: one table for Products, … Read more

When/Why to use Cascading in SQL Server?

by

Summary of what I’ve seen so far: Some people don’t like cascading at all. Cascade Delete Cascade Delete may make sense when the semantics of the relationship can involve an exclusive “is part of” description. For example, an OrderLine record is part of its parent order, and OrderLines will never be shared between multiple orders. … Read more

What should I name a table that maps two tables together? [closed]

by

There are only two hard things in Computer Science: cache invalidation and naming things— Phil Karlton Coming up with a good name for a table that represents a many-to-many relationship makes the relationship easier to read and understand. Sometimes finding a great name is not trivial but usually it is worth to spend some time … Read more

Implementing Comments and Likes in database

by

The most extensible solution is to have just one “base” table (connected to “likes”, tags and comments), and “inherit” all other tables from it. Adding a new kind of entity involves just adding a new “inherited” table – it then automatically plugs into the whole like/tag/comment machinery. Entity-relationship term for this is “category” (see the … Read more

Is there ever a time where using a database 1:1 relationship makes sense?

by

A 1:1 relationship typically indicates that you have partitioned a larger entity for some reason. Often it is because of performance reasons in the physical schema, but it can happen in the logic side as well if a large chunk of the data is expected to be “unknown” at the same time (in which case … Read more

How to Store Historical Data [closed]

by

Supporting historical data directly within an operational system will make your application much more complex than it would otherwise be. Generally, I would not recommend doing it unless you have a hard requirement to manipulate historical versions of a record within the system. If you look closely, most requirements for historical data fall into one … Read more

Database Design for Tagging [closed]

by

Here’s a good article on tagging Database schemas: http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/ along with performance tests: http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/ Note that the conclusions there are very specific to MySQL, which (at least in 2005 at the time that was written) had very poor full text indexing characteristics.

Relational table naming convention [closed]

by

Table • Name recently learned singular is correct Yes. Plural in the table names are a sure sign of someone who has not read any of the standard materials and has no knowledge of database theory. Some of the wonderful things about Standards are: they are all integrated with each other they work together they … Read more