Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.

learn more… | top users | synonyms (3)

2
votes
1answer
64 views

How to handle “or” possibilities in MongoDB

I'm new to MongoDB and by no means an expert in DB design in general. This feels like a really basic question that's probably been answered a million times, but I'm having a surprisingly hard time ...
2
votes
1answer
253 views

One to one mapping vs many to one mapping on data base design

I'm currently doing call detail records (CDR) data analysis as my project. On course of data-base design, I had separated the date (table contain id, day,month,year), time field (table contain id, ...
2
votes
1answer
221 views

MySQL Performance Gains By Splitting Tables?

I am designing a database schema to be used with MySQL. The database needs to keep track of a users basket items and purchased items. I've thought about two ways I could do this but not sure which ...
2
votes
1answer
544 views

Multiple Database Design or NOT?

I have a database design issue, one that has rattled my brain. What is the project? I am designing a platform where different people register a group, each group is allowed to register their own ...
2
votes
1answer
145 views

What are the risks for logging across databases via a trigger?

I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here. On SQL Server 2005, we currently log certain table changes via triggers using ...
2
votes
1answer
2k 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." ...
2
votes
3answers
1k 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 ...
2
votes
2answers
516 views

How to design future proof tables?

I am new to database design and have started developing my first schema. Some of my tables (e.g. user_profile) I am struggling to come up with an exhaustive list of fields for the table. A particular ...
2
votes
1answer
217 views

How to design functionality to comment about a user joining a Facebook group?

User A joined the group DBA The table responsible for users x groups is "Following" table. user_id group_id 1 1 1 2 2 1 And ...
2
votes
2answers
147 views

What is the way to paginate last updated rows from multiple tables?

I want to have something like a newsfeed from multiple objects sort by most_recent. However I need to paginate (load more/offset) the results. Row1 = Post's - text, username Row2 = Photo's - text, ...
2
votes
1answer
951 views

How to model a user/group relationship where users are assigned an unique number within the group?

I am working on a web-server applications that deals with 2 entities: users groups A user can belong to one, several or no group at all. Fairly usual, I assume. However, I have additional ...
2
votes
1answer
248 views

Application with “configurable” columns : how to model this

I'd like to do a status list. I have 1..n projects. Each project contains 0..n tasks, for which I'd like to report a status, next phase, priority, etc... For each project, I'd like to setup which kind ...
2
votes
2answers
378 views

Please review my first thought-out attempt at a database schema

I am researching database design and theory and have finally made a thoughtful attempt at creating a schema that would be based upon SQL. I would like, if someone has time, to review my layout and ...
2
votes
1answer
613 views

Database design with different user roles connected by different relationships to one organizaiton table

An user is connected to an organization by some role. Lets say there are 2 roles freelancer and employee. One employee can only be a part of one organization and if he is an employee then he can't be ...
2
votes
1answer
5k views

What is the best database design for multi-language data?

I am designing a multi-language database for a piece of software with the following features: data used in every country differs from other countries data used in every country will be in 2 ...
2
votes
1answer
98 views

Help with database design

I'm quite the beginner when it comes to databases, so please bear that in mind. I need to store user statistics in several categories for an application. In the near future I will most likely need ...
2
votes
1answer
241 views

Representation of circular references constraint

We have a database where we store commands and properties for devices that may be controlled via a network. For instance, "Concrete Device" is an instance of some DeviceType, which in turn contains ...
2
votes
1answer
2k views

Why is a Star Schema more normalized than a 3NF Schema?

According to Oracle's documentation, third normal form schemas "may require less data-transformation than more normalized schemas such as star schemas". I'm confused, I thought 3NF is the most ...
2
votes
1answer
249 views

From a design perspective, what is the difference between an inventory system and an asset tracking system?

From what I can see, the database design is similar. One is the consumables that are resident in a warehouse (inventory). The others are items, capital, that are assigned to people or places, but ...
2
votes
1answer
277 views

Is having all entity tables inherit from one main entity table a bad idea?

My co-worker is designing a schema where most entities (like contacts, quotes, tasks) inherit from an "entity" table, which keeps track of things like which tenant owns the entity, and chagnes to the ...
2
votes
2answers
119 views

Long JOINS Returns No result all tables have one to many relationship with a particular table [closed]

I have a Table Enterpries with Primary Key 'RCNO'. All other tables have a one to many relationship with Enterprise via it's RCNO column. I make a Join of all the tables and i get nothing. Below is ...
2
votes
2answers
258 views

creating a database scheme for storing information about an item, which option is better?

I have a database which stores information about various items. In particular, for each item I need to store the name, type and color. Each type can have a various amount of different colors. For ...
2
votes
1answer
156 views

Is a year-to-date dimension the same as a time dimension?

If I have a table such as...: YYMM | Sales ------------ 1201 | 10 1202 | 20 1203 | 30 1204 | 40 ... then I might describe this as having a time dimension. If I wanted to calculate a year-to-date ...
2
votes
1answer
136 views

ERwin Data Modeler Support

For the sake of not asking 5,000 questions here; are there any reliable sources of information about ERwin? I have to use it for a course and I have been unable to find any website or documentation ...
2
votes
1answer
115 views

Querying a big database

I am PHP programmer with basic-intermediate knowledge on databases. I am working on a project where I need to verify a given phone number's presence in India's Do not call database. Following is the ...
2
votes
1answer
356 views

Referring to super parent or as a separate entity?

I have this person table as super parent, id firstname lastname email telephone ... ... and user table as a child id person_id (FK) password username screenname ... ... They must be 1:1 ...
2
votes
2answers
105 views

Adding Security to a legacy DB with Schema

I am working on a DB where almost every technical user has full read / write permissions on all tables. We are working towards removing permissions, gradually, from certain users, and I had hoped that ...
2
votes
2answers
348 views

Best index strategies for read-only table?

I have a table in SQL Server which has following characteristics: will contain about 1.2M records. first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each ...
2
votes
1answer
350 views

determining user types upon authentication

I've got 3 different types of users (entities) that will log into a website in developing for a client. "FITTER", "CLIENT", "ADMIN" the first method I came up with was this. CLIENT TABLE | ...
2
votes
1answer
366 views

In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?

I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and ...
2
votes
2answers
364 views

Database Transfer from PostgreSQL to MySQL

Is there any method to tranfer the data and schema from a postgresql database to mysql?
2
votes
1answer
706 views

Trusted Constraints and NOT FOR REPLICATION

Version and Build: SQL Server 2005 SP4 (9.0.5000) As a disclaimer of sorts, I am asking this question to generate some discussion and/or debate on the topic. I don't know if there is really a ...
2
votes
1answer
672 views

Database Design: Storing Map Locations

When working on a game, I developed a database that used X/Y coordinates, roughly as: CREATE TABLE Map ( MapId INT IDENTITY PRIMARY KEY, X INT NOT NULL, Y INT NOT NULL, North INT ...
2
votes
2answers
5k views

Database Design for Airline Reservation System

I'm just a beginner seeking some answers. I'm creating a very simple database system for an airline company, just for learning purposes. Here are my questions: How to show relationships for entities ...
2
votes
1answer
277 views

MySQL data modeling help

I've been struggling with this model for weeks, going in circles from one type of solution to another. I'd like some advice on the best approach. One or more organizations can sponsor an event or ...
2
votes
1answer
18 views

column identifier versus separate table with related data with different purposes

Very new to database management here. I'm trying to figure out what rule of thumb I should be following when it comes to making separate tables for data rather than just labeling it as different ...
2
votes
1answer
33 views

Database design, many rows or many columns?

I am aware that this has been discussed before, but I've read conflicting information about this particular topic, so I'm hoping for a bit more decisive opinions from you. I have multiple tables, ...
2
votes
1answer
41 views

Database Table Design

I would like to ask couple of questions regarding my schema table design. I have the following data coming from an external database where I do not have any control over it. PROD_NO PROD_NAME ...
2
votes
1answer
176 views

Is this acceptable practice for a many to many?

As a newbie in database design i have a question about many-to-many relationship practice. This question is actually a follow up question of this question, but i found it to big to explain in a ...
2
votes
1answer
97 views

How to write a query, with multiple foreign key in mysql

I am working on a simple PHP and MySQL college level clinic management system. There 4 main table which relation with foreign key. I'm little bit confusing multiple foreign key in table .. what would ...
2
votes
1answer
58 views

10000 tenants and Muti-Tenant Data Architecture performance?

We are trying to decide on a database design for a new web application. We expect to have close to 10,000 tenants and would like to keep their data in separate databases if it makes sense to do so. ...
2
votes
1answer
31 views

Design for m-to-n relation with unidirectional attribute(s)

Assume I have a Table StackBox that holds items as Item. I allow StackBox to have multiple Item and I allow Item to be in different StackBox. It is obviously an m-to-n relation. Solving this with an ...
2
votes
1answer
23 views

Attribute-Attribute Dependency

So as part of our college project, I'm working on CENSUS Database. I stumbled upon a problem wherein if an individual is below the age of 18, his/her marital status should only allow 'NA' and anyone ...
2
votes
1answer
48 views

Why wouldn't I put the single preferred option into its own table, instead of having a 'Preferred' column?

In similar spirit to How to implement a 'default' flag that can only be set on a single row I would like to specify that, for any given list of possible names for a business, only one of the ...
2
votes
1answer
67 views

BLObs – Should I Store In Database Or in Directory Structure?

BLObs – Should I Store In Database Or in Directory Structure I have been developing a database to replace an ageing data structure that contains 1000's of files within a directory structure. The ...
2
votes
1answer
79 views

Storing floating point values for various units of measurement (kg, ml, mm, pl)

I have a table like the following: order_item ------------ serial | id (PK) integer | order_id ???? | quantity integer | unit_of_measure_id (PK) --> ml, l, kg, mg, mm, m, etc. ...
2
votes
1answer
90 views

If A and B are candidate keys, then AB is candidate key or not ?

If R{abc} is a relation, f{ a->b b->a a->c b->c }, then a and b are candidate keys. What about ab ? Is it a candidate key or is it a super key or both ?
2
votes
1answer
103 views

Compare the structure of two MySQL Databases

I have two MySQL databases that are very similar to each other. How can I find out the differences in tables, and the differences in columns in each table? the databases are in different schema. ...
2
votes
1answer
554 views

Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating. The database I'm using is postgres. That's not really something I can or want to change (unless there's very good ...
2
votes
1answer
509 views

How to return a record from function, executed by INSERT/UPDATE rule (trigger)?

I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4! Here is an SQL fiddle. The ...