Category of database management systems characterised by the ability to join two data sets by matching on common fields, using mathematics-based relational logic.
13
votes
5answers
607 views
What standard should I follow when naming tables and views?
What standard should I follow when naming tables and views? For instance, is it a good idea to put something like tbl_ at the beginning of table names? Should I designate code/lookup tables in some ...
10
votes
4answers
3k views
How should I design a relationship table for friendship?
If A is a friend of B, then should I store both values AB and BA, or one is enough? What are the advantages and disadvantages of both methods.
Here is my observation:
If I keep both then I have to ...
9
votes
3answers
932 views
Proper Use of Lookup Tables
I'm having trouble figuring out exactly how to place good boundaries for when and where to use lookup tables in a database. Most sources I've looked at say that I can never have too many but, at some ...
7
votes
2answers
149 views
Best relational database structure for this data
I'm in the process of creating a database scheme for the following scenario:
There are users
Users have roles (such as "Developer" or "CEO")
Roles have applications (such as "Topdesk")
Applications ...
6
votes
2answers
960 views
Is one-to-one relationship normalized?
Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table ...
6
votes
2answers
786 views
How do Application Like Yelp Retrieve distance information from data base efficiently
For example, say I have a table:
Business(BusinessID, Lattitude, Longitude)
All are indexed of course. Also there are 1 million records
Say I want to find businesses closest to 106,5, for example, ...
6
votes
3answers
114 views
Arbitrarily ordering records in a table
A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
6
votes
3answers
698 views
How to normalize Time Sheet data?
I am having a terrible time finding an appropriate way to store this data in a database.
The current excel (simple) version looks something like this:
http://i.stack.imgur.com/BEZOw.png
Staff and ...
6
votes
2answers
183 views
Definition for “logical difference”?
I'm currently reading "SQL and Relational Theory" by C.J. Date. And although I'm quite far in the book, I've got a few basic questions. I want to know what the term "logical difference" means, The ...
5
votes
2answers
522 views
How to structure a model to properly and efficiently represent tree-like data on relational databases?
Based on Traversing tree-like data in a relational database using SQL question, I would like to know how the way regularly used to describe tree-like data on relational databases considering physical ...
5
votes
4answers
180 views
Database Design for updatable sequential records
I am working on creating a data model for storing data related to production tracking. I work for an engineering firm that models and analyzes data for our clients. There are several steps in the ...
4
votes
3answers
518 views
How to have a one-to-many relationship with a privileged child?
I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
4
votes
1answer
228 views
Database structure, relational or data warehouse?
Good morning,
Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing the following columns:
Date ...
4
votes
2answers
6k views
Relational Design - Multiple tables into one foreign key column?
Take a table that tracks meat purchases. It has a "meat_id" foreign key column to indicate what type of meat the purchase was.
But, the different types of meat are unique in some way (such as USDA ...
4
votes
1answer
103 views
How should I model a worflow of status/activities ? (rules like “if is 3 go to 4, if is 4 go to 5” etc)
I'm trying to model a workflow (status and activities) to create "rules".
Let me give you an example.
The main "object" let's say are "vehicle requirements", I will have this table:
idRequirement, ...
4
votes
1answer
107 views
unique constraint on a set
With reference to this SO question, what would be a correct way to implement Unique tag Combination Constraint in SQL Server 2005?
To illustrate the problem, I make an example:
A tag_bundle consists ...
4
votes
1answer
224 views
Would appreciate some suggestions on how I should model this data
I am setting up a website that will have users. Users can search for songs and (if signed in) save them to a playlist or their library of songs.
I want it to be like iTunes in that all the songs on ...
4
votes
1answer
477 views
Two relationships to same table
i have one transaction table, each transaction has 5-6 contacts involved. each contact has the same fields like name, address, cell, email etc.
So the best thing I guess is to use the same contact ...
4
votes
2answers
397 views
What's a good relational structure for units and complex unit conversions?
My company is in the Energy industry and I need to come up with a good way to represent the conversion of units of measurement. I've done some searching and have yet to find a good article covering ...
4
votes
1answer
107 views
How can I improve this design?
I'm creating an event planning PHP/MySQL web application as a learning project. The whole app revolves around the idea of the event and its various components (participants, groups, lists, etc.)
I ...
4
votes
1answer
198 views
Friends relations in MySQL
I'm developing a friendship relation in MySQL where friend relation is mutual. If A is friend of B, then B is friend of A. If one of the users end friendship then relation drops. I want to learn which ...
3
votes
2answers
121 views
Which tables to create according to relations
We are planning a new system with a new DB (MS SQL Server).
We have the following entities and the following relations:
Entities:
Clubs,
Chains,
PaymentTypes,
etc...
Relations:
Clubs-Chains (M-M);
...
3
votes
1answer
76 views
Optimised Schema for City-to-City Distance Table
This is my first question, forgive me if this question is simple. I'm stuck here, trying to implement the relations for the data represented below. I want to scale it vertically (new rows), instead of ...
3
votes
1answer
256 views
What scope(s) is “hasMany” a part of?
I realize, when talking about structures, obviously if you say "post hasMany comments" there are many records about comments relating to each post.
What I don't understand is what context these sort ...
3
votes
1answer
221 views
How to use a relational database to store all users who belong to a group, and all groups which a user belongs to?
This is a beginner question:
A user can belong to many groups, and a group can contain many users.
Let's say the two tables look something like this:
user_id
user_name
user_email
group_id
...
3
votes
3answers
200 views
How to design this simple DB?
How to design this simple DB?
I need to add some things to a DB I use to keep track of servers.
Table looks something like this.
Matrix
| Name | Description | Etc | Etc |
...
3
votes
1answer
451 views
Designing a user authenication (Roles & Rights) module
I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to ...
3
votes
1answer
407 views
database design issue
This is my first time on this beautiful content rich site. I have a sql server 2008 database, but am not too experienced with good relational database design.
My project is this:
I have a school ...
3
votes
1answer
39 views
Create Scheme that allows for fluidity between entities
We are a small rapidly evolving company and recently investigated switching databases from SQL Server to a graph database. We like the graph approach because of the fluidity and openness that it ...
3
votes
1answer
167 views
Applying user-defined fields to arbitrary entities
Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and ...
2
votes
2answers
126 views
MySQL - Normalization over meaningless data - Compound Primay Keys or Surrogate ID field
Please forgive my title; if you have a suggestion for it, feel free to comment.
I have a database:
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`isbn` VARCHAR(255) NOT NULL,
`title` ...
2
votes
3answers
193 views
First Normal Form, why is it good and how does it reduce redundancy
I asked a similar question on SO and was advised to ask the type of question here. This is for a course on relational databases. A sample problem asks "how come every table in a relational database ...
2
votes
3answers
229 views
Express union which is dependent on other results
I would like to make a union of two tables that are actually in a hierarchical constellation.
How would I write that in relational algebra? Say table A is the parent of B in a 1:n relationship.
...
2
votes
2answers
188 views
Advanced search engine or server for relational database
In my current project we are storing big volume of data in relational database. One of the recent key requirements is to enrich application by adding some advanced search capabilities.
In the Project ...
2
votes
2answers
118 views
mySQL Relational Tables
Say I have 3 tables Products, Orders and Orders Product
Products Table
ID | StockNum | Description | Price
4 | 452SB | Black | 45.65
5 | 752SA | Green | 17.12
Orders Table
ID ...
2
votes
1answer
132 views
How can I best combine “get categories” and “has user access”?
I was wondering if it's possible to create a query for the following problem. I only want to select categories from the (MySQL) database (if the user has access).
A user has multiple roles;
A ...
1
vote
1answer
81 views
Record with variable number of columns?
I am trying to model a cable mapping for devices. Each device has multiple cards, and each of those cards has multiple ports. Since both the number of cards and ports vary, I am confused on how to ...
1
vote
1answer
350 views
How to model entities in an entity relationship diagram?
I'm a beginner Database designer and wondering if anyone can help me understand this.
Let's say we want to design a system that has, among other entities, an entity called Vehicle, and another one ...
1
vote
2answers
284 views
Database Design - One table for different types of transactions
Considering a procurement system as an example. Would it make sense to have one single table for the following: Quote, Order, Invoice and Credit Memo?
There will be some details that are different. ...
1
vote
2answers
65 views
Table Design - Identical tables for different statuses
I am developing a transactional System and the below is a simplified scenario of my problem.
Users enter JOBS in a system.
A User first enters a JOBS with a DRAFT status (and the Job is given a ...
1
vote
3answers
146 views
Database Design Relationship for Patient Visit in MS Access
I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have.
There are a total of 6 ...
1
vote
1answer
70 views
Database Relational Design for Multiple Categories
I am building a database for participants who are grouped into studies. There's a total of 5 different studies - A, B, C, D, and E. The participants may be in more than one studies, and participants ...
1
vote
1answer
41 views
Correct data model for users with clients
I have an application where users are granted access. Part of the system allows users to have their own 'clients'.
I have a users table
********************************
users
...
1
vote
2answers
1k views
MySQL Workbench, how to refresh EER diagram?
I'm using MySQL Workbench to design my MySQL database and model how entities would look like and how they would relate to each other. Now, sometimes I use Doctrine ORM. When using Doctrine ORM, I ...
1
vote
1answer
90 views
What is the correct way to store a list and its items in this instance?
The app I'm working on revolves around projects. Each project can contain several lists, and each list is made up of several items. Each list relates to a single project.
Is it better to have one ...
1
vote
1answer
315 views
Insert bulk Parent and Children rows using SQL Query
We receive bulk data from our customers in a spread sheet. I loaded them in a temporary table as of now. I tried to normalize the data and create a parent table and each parent to have 4 or 5 child ...
1
vote
1answer
81 views
Separating tables vs having one table
At the moment I have a table setup that looks somewhat like this:
create table tbl_locationcollections
(
id int(11) PRIMARY KEY IDENTITY, --(Primary Key),
name varchar(100) not null,
...
1
vote
1answer
96 views
Switching to NoSQL for native JSON use?
I am rather comfortable writing concrete schemas with explicit primary keys, foreign keys, indexes, views and stored procedures. For queuing I use a NoSQL solution called: Redis.
Recently I heard a ...
1
vote
1answer
111 views
Modeling a ticket system based on a set of disparate kinds of tickets?
I'm working on a project that allows for the creation of "support tickets." These are not confined to something that needs fixing, however, so in a way they could more accurately be called "jobs."
...
1
vote
1answer
65 views
Can I place the attribute in the Entity, when the foreign key is also already present?
I am using DBdesigner Fork for generating ER diagram.
I have the 'Company' as an entity for which can I place the attribute of 'idShares', despite of having the foreign key of Shares Entity in the ...
