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 (4)

3
votes
1answer
82 views

Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)?

I am trying to improve the data storage in biomedical science lab where I recently started working. The existing workflow is atrocious, involving many differently formatted Excel sheets that all get ...
0
votes
0answers
89 views

Design database schema for membership application with fees and purchases

My membership application needs to record payment of membership fee, card fee and misc. fee for each member. I also need to record purchases of each members and payments for the purchases.
2
votes
0answers
93 views

Storing phone number in E.164

TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field? Have been reading up on format/storage of ...
2
votes
1answer
190 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 ...
1
vote
2answers
38 views

Trying to design database for eSports Teams - Issue with dependencies

I am trying to design a database in Access for an eSport league (specifically League of Legends North America). I will post what I have and then explain my issue. My issue comes when I try to ...
2
votes
1answer
66 views

Table structure for voting app

I have an app where a user votes between two local profiles on which one is better. I want to ensure that the same user cannot vote more than once between the same two profiles (say the user votes on ...
0
votes
0answers
32 views

How can I correct my untrusted foreign key situation?

In our scenario, we have a number of situations in which foreign keys or check constraints are not trusted. Here's some example situations: A "claim" is an entity that is the primary purpose of the ...
0
votes
2answers
47 views

Approach to take regarding a field that is frequently re-computed

I have a few fields with text data in a main table which is processed in a certain way to produce a processed version of that piece of text. Now, the way in which this text is processed is expected ...
1
vote
0answers
60 views

Database design/layout

I have an objective to record and store test data. A secondary objective is to track the progress of the test programme (it is a large programme). So I want to use MySQL (not Access or DOORS) to ...
1
vote
1answer
56 views

Having Non-Numeric values as measures in Fact table

According to my research regarding having non-numeric value as measures, I found that having non-numeric won't be an optimal one. So I planned to maintain numeric values. My scenario was "Employee's ...
2
votes
2answers
251 views

Advice on a basic design, first time database design

I am taking a course to become a Java developer. The course involves using databases but we never really design any unfortunately. Most of the time we get pre-made databases and we have to implement ...
0
votes
1answer
17 views

Fan trap and redundant relationships issues

Here're a few domain rule resulting the current design of the relations. A project application form can have only one company responsible supervisor, and one company where the project is conducted. A ...
1
vote
1answer
50 views

Database templates [duplicate]

Are there any site with database templates for specific purposes? (i.e. a database schema for users and authentication in a webpage) for different databases like Oracle, MySQL... I have been looking ...
0
votes
0answers
15 views

Does an Object literal always mean the Predicate and Object form a key-value property on the Subject?

A triple is Subject-Predicate-Object, e.g. Post Office - Not Open - Sunday. Consider another example, Post Office - Size in Square Feet - "4000" For this example, I have two observations: the ...
1
vote
1answer
40 views

Computed Columns Or Views - Database Table Design

I want to store values in certain units along with their corresponding conversions. So for example, there would be following columns DateTime Millimetres Centimetres Metres For a ...
1
vote
0answers
32 views

Managing logs generated by user

I am developing a web app for my client and there is a section which displays the changes made in the application by any of the user. This section is known as log.It basically stores and displays the ...
1
vote
0answers
16 views

DBMS for working with sets [closed]

I want to simulate some stochastic system which deals a lot with small sets of integers, e.g. [0,1,5],[2,5,7,9,11]. The number of the sets is so large, and I need to look them up so often that it is ...
1
vote
1answer
30 views

How to Prevent a Many to Many When Number of Rows Shared Is Unknown

I have two tables that look like the following: Subjects id|subject_name|..some other columns 1 |maths | 2 |english | 3 |science | Users id|username|...some other columns 1 |Frank1 | ...
1
vote
1answer
91 views

Storing multi-select data with single-select data

I've got a database with some multi-select fields. To keep it simple, let's just say those fields are MatchGender and Interests. I want users to be able to find people who match (hence Gender is a ...
1
vote
1answer
28 views

How to model state change timeline

I've got an app where users can place orders for recurring delivery of a particular service. The orders table has columns service_id and state, where service_id links to the service the user has ...
3
votes
3answers
78 views

Is it better to create another table to possible null values in relational databases?

I'm designing a relational database to use in MySQL. I have the next situation: In one table there are fields that in the most of situations are NULL. The fields would be like: Table name: tabla ...
0
votes
0answers
72 views

DB schema for an invoice system with tiered prices

I've been hired by a payment gateway company that handles payments for its customers. Every month, a script is run and will charge each merchant for the bulk number of payments completed. The unit ...
1
vote
2answers
81 views

Is this db/table structure sensible?

I'm not very experienced in database design so bear with me. I've taken over a ruby/rails app. with a connected MySQL/innodb database. As I'm updating the server to the latest ruby/rails version I ...
0
votes
1answer
32 views

Entries with 2 variables. Do I need to make 2 more tables by entries?

In the case of shipment carriers, I have a table with products which price and delay will vary with 2 variables. PRODUCT | SHIPPING DATE | PRICE Carrier 1 | A | B Carrier 2 | ...
-5
votes
1answer
131 views

How to select only few columns from a table in database? [closed]

I have a table which consists of around 200 columns where name of each one is col_i (i=1 ,2,..200).Suppose I have to select only first 50 columns, how should I proceed? I found about concat() function ...
1
vote
1answer
46 views

Duplicate records referenced across different databases

I have a table holding 'People' data (about 70K records) that coexists with an 'Address' table so there's one AddressID per every person in the 'People' table. The idea was to centralize the data in ...
0
votes
2answers
102 views

Large database design

I am new to database design and despite my research I can really only find basic examples on design which does not help for my situation. Currently using MySQL The problem I have is as follows: I ...
0
votes
0answers
67 views

Referral table structure

I need to make a simple referral system and I have a database table set out as follows: mem_id | date | refer_email | accepted And the meaning of such columns is the following: mem_id: The ...
2
votes
1answer
124 views

Have foreign key constraint and index key for the same column(s)

I am creating a composite index key for 2 columns and then adding foreign keys to its appropriate table. Do I need to create a multiple index key reference and then create separate foreign keys? My ...
1
vote
1answer
54 views

Multi-tenant database constraints

I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other. My question is, is there any sort of way that I can use ...
1
vote
1answer
78 views

How to structure this data? (Many columns currently.)

Working in MariaDB, we're trying to figure out how to store this data. All of our other tables have been normalized and are working nicely, but this has me stumped. We have a number of objects and ...
0
votes
0answers
13 views

Determining the 3NF based on functional dependencies

Given a relation R = {E M L T} and functional dependancies LT -> E, ET -> L, TM -> E, and E -> M , I know that the superkeys of this relation is TL, TE, and TM. For 3NF, the left hand dependancies ...
1
vote
0answers
20 views

Null Dimensions in SSAS [closed]

Several years ago 2008, I create a data warehouse using Business Objects. Business Objects did not process Outer Joins well and used SQL for all of its queries. Therefore, for every dimension I ...
0
votes
1answer
85 views

How to store iterations of similar datasets

I have a dataset that is stored across 20 or so tables, most of which have a few million rows. This data will be generated fresh each month, and compared to past datasets. I'm pretty new to ...
0
votes
1answer
63 views

String to timestamp in datastage 9.1.2

Apologies for asking a question that I suppose is very basic. I'm just starting out with Streams. I'm trying to read a sequential files containing a timestamp field and a value field, like this: ...
0
votes
1answer
59 views

Database design: handling time

I am developing a weekly timetable which ideally would allow teachers pick what subjects they teach. While most of this is fairly straightforward, from a conceptual point of view I am unsure how to ...
1
vote
0answers
89 views

best way to log large amounts of data

I'm building a new logging system for our company. I'm wondering about the best way to store about 50-100GB data each year. I'm looking at both MySQL and MongoDB. I'm considering MongoDB because it ...
0
votes
0answers
19 views

What factors would cause me to favor inheritence over composition in a small database?

I have identified two choices for how to model addresses in my corporate entity database. Method 1: inheritance Table 1: Addresses [data table, primary key = (id)] id (integer) - primary key ...
0
votes
0answers
42 views

Loop through fields on a before-insert trigger?

Here's my table: CREATE TABLE foo ( i_am_pk CHARACTER VARYING(255) PRIMARY KEY NOT NULL, dont_touch_this CHARACTER VARYING(255) NOT NULL, name CHARACTER VARYING(255) NOT NULL, addr CHARACTER ...
1
vote
2answers
86 views

Three questions about Database design

I'm new to mysql,and I'm designing a database for my social network app, I got some questions about it. user_table comment_table 1.Should I use unique key as much as I could in the database ...
2
votes
2answers
226 views

Is it acceptable to have circular foreign key references\How to avoid them?

Is it acceptable to have a circular reference between two tables on the foreign key field? If not, how can these situations be avoided? If so, how can data be inserted? Below is an example of ...
3
votes
1answer
48 views

Should I create a extra relation-table when I have two columns in one table referencing to one column in another table

My problem is best explained by example: Suppose I have a database about chess games and players. Which one of the following structures should I choose Option 1) player (player_id,name... etc) ...
0
votes
0answers
43 views

DB Design for Mobile App Clients

Hello I am new to designing DB for Mobile App where speed is the Key. My Mobile App will request to WebAPI and WebAPI will get data from MySQL. I have designed my tables in MySQL as per relational ...
1
vote
2answers
46 views

Should a table have a foreign key to another table if it already has a foreign key to another table that has that foreign key?

That was a hard title to digest even for me so I'll break it down. I'm very new to SQL, and I just designed my first database. I also am not very good at JOINS which might be the answer to my ...
0
votes
1answer
51 views

Creating many-to-many relation on demand?

Users can join projects. Projects can have many tasks. Each user completes the task at their own time, they can also have some other user specific settings on the task like the priority in their ...
1
vote
1answer
38 views

Is this a good way to model course assignments?

I'm having trouble designing a data model that allows for the following: Courses can have many assignments. Assignments can be defined recursively. e.g. "There is reading homework every tuesday". ...
0
votes
1answer
56 views

Entity ID field that can refer to multiple tables?

In designing both audit tables and notification tables, I've run into an issue where I need a row to refer to an entity in another table, but which table that entity is isn't always the same. For ...
0
votes
1answer
45 views

Creating a flexible table containing prices and periodes

I'm creating a system of availability for rooms and with a calendar according to the days, with every day have it's own price. Room Type | 01 | 02 | 03 | 04 | 05 | ... | 31 | ...
0
votes
0answers
35 views

table design approach for a rental application with several one to many relationships

I'm trying to go about designing a database/tables for the following situation I'm being presented. I'm developing a rental web application with MySQL as db engine. It is for a nonprofit organizations ...
1
vote
3answers
78 views

designing a database with type and sub type

I am trying to build a publications database, and i want to develop a genre relation that has various (maybe look up tables) sub-genres attached. as an example, in the genre relation there would be a ...