Tagged Questions
14
votes
2answers
9k views
What is the purpose of system table table master..spt_values and what are the meanings of its values?
What is the purpose of system table table master..spt_values?
Why was it provided and how one should use it?
What are the meanings of its type, low, high values?
Update:
Google search gives ...
102
votes
13answers
33k views
Subqueries vs joins
I refactored a slow section of an application we inherited from another company to use an inner join instead of a subquery like
where id in (select id from ... )
The refactored query runs about ...
2
votes
3answers
658 views
How to create multiple one to one's
I have a database set up with many tables and it all looks good apart from one bit...
Inventory Table <*-----1> Storage Table <1-----1> Van Table
^
...
43
votes
6answers
17k views
What are best practices for multi-language database design? [closed]
What is the best way to create multi-language database? To create localized table for every table is making design and querying complex, in other case to add column for each language is simple but not ...
77
votes
12answers
18k views
Database Design for Tagging
How would you design a database to support the following tagging features:
items can have a large number of tags
searches for all items that are tagged with a given set of tags must be quick (the ...
37
votes
8answers
18k views
Is it possible to query a tree structure table in MySQL in a single query, to any depth?
I'm thinking the answer is no, but I'd love it it anybody had any insight into how to crawl a tree structure to any depth in SQL (MySQL), but with a single query
More specifically, given a tree ...
43
votes
6answers
24k views
Database Structure for Tree Data Structure
What would be the best way to implement a customizable (meaning, a tree structure with an unknown number of level) tree data structure in a database?
I've done this once before using a table with a ...
12
votes
4answers
3k views
multiple fixed tables vs flexible abstract tables
I was wondering if you have a website with a dozen different types of listings (Shops, Restaurants, Clubs, Hotels, Events) that require different fields, is there a benefit of creating a table with ...
14
votes
4answers
11k views
db design - survey/quiz with questions and answers
I'm working on a fairly simple survey system right now. The database schema is going to be simple: a Survey table, in a one-to-many relation with Question table, which is in a one-to-many relation ...
38
votes
11answers
11k views
Is there common street addresses database design for all addresses of the world?
I am a programmer and to be honest don't know street address structures of the world, just how in my country is structured :) so which is the best and common database design for storing street ...
7
votes
4answers
3k views
Why (and how) to split column using master..spt_values?
Subquestioning the answer to question "Split one column into multiple rows" which I re-wrote here as [ 1 ].
What is the (meaning of) Type = 'P' and why to use undocumented master..spt_values for ...
32
votes
13answers
15k views
Dynamic Database Schema
What is a recommended architecture for providing storage for a dynamic logical database schema?
To clarify: Where a system is required to provide storage for a model whose schema may be extended or ...
7
votes
3answers
1k views
Name database design notation you prefer and why?
Which notation, methodology and tools for database designing, modeling, diagraming you prefer and why?
Which notation, standards, methodology are the most broadly used and covered by different ...
6
votes
9answers
1k views
Relational database design question - Surrogate-key or Natural-key?
Which one is the best practice and Why?
a) Type Table, Surrogate/Artificial Key
Foreign key is from user.type to type.id:
b) Type Table, Natural Key
Foreign key is from user.type to ...
55
votes
15answers
55k views
Facebook database design?
I have always wondered how Facebook designed the friend <-> user relation.
I figure the user table is something like this:
user_email PK
user_id PK
password
I figure the table with user's ...
facebook
