Category of database management systems characterised by the ability to join two data sets by matching on common fields, using mathematics-based relational logic.
0
votes
1answer
13 views
JOIN to read first table and get possible relationship in the second table
Consider a main table as
CREATE TABLE groups
(
group_id int(11) NOT NULL AUTO_INCREMENT,
group_title varchar(255),
PRIMARY KEY(group_id)
) ENGINE=InnoDB
and a second table for relationship as
...
1
vote
1answer
32 views
How would I change this relational model to fit a nosql database?
Because of things beyond my control, I have to switch from a relational database to MongoDB for the advertising platform I'm helping to build. I've got a relational schema sketched out, but I'm unsure ...
0
votes
1answer
25 views
Candidate key = Key = Minimal Superkey?
I got a little confused by all these keys. As I understand
A key of a relational schema =
The minimal subset of a superkey that is still a key =
A candidate key
Is this correct or not?
0
votes
1answer
68 views
Index optimization
I'm building a 'tag' system for my blog posts. Currently my tags table has columns tagid - MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY and tag VARCHAR(30) NOT NULL UNIQUE.
And I have another table ...
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,
...
0
votes
1answer
40 views
Is it better to use multiple one-to-many relationships, or polymorphic?
I've got the following tables:
Clicks table
Websites table
Ads table
Both Ads and Websites have many Clicks (i.e. both models have a one-to-many relation with Clicks). In such cases, is it better ...
1
vote
1answer
105 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 ...
0
votes
0answers
58 views
Use cases for NoSQL databases: Time series
I need to set up a database that will record temporal series data (sensors). A friend of mine has recommended me to use a NoSQL database instead of using Oracle (for instance).
There're also some ...
1
vote
1answer
70 views
Delete entry from related tables
I can not use InnoDB engine because of some dependency troubles in LuaSQL and PtokaX.
My current query is something like:
DELETE e.*, m.*
FROM `entries` e
INNER JOIN `magnets` m
ON ...
0
votes
1answer
66 views
Foreign Keys with Composite Key
Imagine I have a table with the following dependency:
(Name, BoughtFrom, TimeBought) --> Cost
Where the three attributes in bold form a composite primary key for the table. Now then, I want to ...
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` ...
1
vote
1answer
83 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
112 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
votes
1answer
77 views
What is the real-world purpose of an optional 1:1 relationship?
I know that one-to-one relationships can be used to split data into multiple tables for performance or security and that it is used to create a is-a-relationsship.
But aside from that things, what is ...
7
votes
2answers
151 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 ...
1
vote
3answers
148 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 ...
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 ...
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 ...
1
vote
1answer
71 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
...
4
votes
3answers
553 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 ...
0
votes
1answer
70 views
Looking for a scalable relational database [closed]
I'm looking for a database that achieves the following:
fully ACID.
relational with joins.
has transactions.
allows the administrator to specify sharding rules (for example: users from Europe should ...
0
votes
2answers
146 views
Relationships in a school database
I am designing database for a school app. Student is the main entity in the database. Student has relation with Parent & Guardian.
I am confused as where to store references. As will student ...
0
votes
1answer
245 views
SQL Server 2008 - How would I create a dynamic query for finding relationships and references to the primary key?
Since I'm new and I've never shared, and yet there are a few useful scripts that I need on a daily basis, I thought I should.
As a new DBA, some data fix DML operations and simple business logical ...
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 ...
1
vote
1answer
68 views
Relation candidate keys
I have the following relation:
R = ABCDE
F: AB->DE, AC->B, DE->C, AE->C, C->AB
I need to determine this relations candidate keys.
Now, if I understood the defintion correctly:
...
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, ...
1
vote
1answer
66 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 ...
1
vote
2answers
288 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. ...
0
votes
2answers
58 views
View for a MANY_MANY_MANY relationship
Let's say I have 3 main tables:
category, tag, url
category and tag have a MANY_MANY relationship (category_tag).
tag and url have a MANY_MANY relationship (url_tag).
I would like for a given ...
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
1answer
355 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 ...
3
votes
1answer
170 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 ...
1
vote
0answers
74 views
SQL relationships, one-to-one, many-to-many etc [closed]
I'm trying to understand all relationship types that can be created in SQL.
So far I only get the "belongs-to" one - Example: a table with "Blog post" records, which contains an author_id column that ...
2
votes
3answers
196 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 ...
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 ...
0
votes
0answers
100 views
When is a relational database worth? [closed]
I have a database with one table which contains a few varchar columns which can contain data from a fixed set (testdata1 can contain three different values in about 10,000 rows).
I thought about to ...
2
votes
0answers
577 views
Schema for an RBAC (Role-based Access Control System)
I want to implement an RBAC, Flat based. I've decided to base my system on the NIST standardization model.
Although all fine and well, I have an inclusion to that design.
Instead of a hierarchy ...
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 ...
0
votes
1answer
433 views
How do I persist recurring events?
I've been tasked with designing a system to recur events. Events may recur daily, hourly, monthly or yearly. Users should be able to schedule events "every 3rd Wednesday of the month" or "The 1st of ...
3
votes
1answer
459 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
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);
...
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 ...
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
43 views
What is this type of query called, and how can I formulate it?
I have following tables:
events(event_id, name, description)
groups(group_id, event_id, title, description)
event_group(event_id, group_id)
users(user_id, name, email)
...
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 ...
3
votes
1answer
227 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
...
2
votes
2answers
119 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 ...
4
votes
1answer
229 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 ...
5
votes
4answers
181 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 ...
