Tagged Questions
0
votes
1answer
67 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 ...
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 ...
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
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 ...
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 ...
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
547 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 ...
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
287 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
1answer
353 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 ...
2
votes
0answers
573 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 ...
3
votes
1answer
458 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 ...
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
226 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
...
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 ...
6
votes
2answers
971 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 ...
0
votes
0answers
492 views
ER to Relational Schema [closed]
I have developed an ERD diagram with UML notation in StarUML.
Now, I need to draw a relational schema but due to lack of features in StarUML i must use other tool.
My question is what exactly goes ...
2
votes
3answers
230 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.
...
4
votes
1answer
226 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 ...
6
votes
2answers
184 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 ...
6
votes
3answers
699 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 ...
1
vote
1answer
164 views
How to design relationships for variant data?
Help me define the table relationships for data with multiple structures. I need to track leads. Leads come from multiple sources. Each lead source has its own data structure. I need advice ...
0
votes
1answer
518 views
Database design for head to head sports
I am trying to create a database to store details relating to darts matches. Im relatively new to this so was wondering if the design I've come up with below would work. It seems I have lots of places ...
3
votes
1answer
257 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 ...
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 ...
0
votes
1answer
683 views
Entity Relationship Model for linked tables
Certainly a noobish question, but I'll ask it anyway. Perhaps someone else will be thankful. ^^
I am getting warmed up with MySQL and I try to understand the concepts of creating well structured ...
5
votes
2answers
525 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 ...
