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)

0
votes
3answers
93 views

Is it better to use an ID (int) or a Name (varchar) as PK for a table? [closed]

Let's suppose I have an ORDERS_LINE table and a PRODUCT table and the ORDERS_LINE table must have the information of which product was ordered. I prefer to use a Primary Key based on the Name since it ...
4
votes
2answers
160 views

Conceptual ERD Multi-table many to many, or possibly recursive?

I'm creating a conceptual diagram [yes, I know I've included attributes and keys - but this is just for me to consolidate what I'm doing whilst learning] -- so please treat it as Conceptual with the ...
1
vote
1answer
22 views

should I change mysql platform for higher innodb_buffer_pool_size?

Currently Im running a 160G MySQL database on Xampp (Windows). But Xampp has upper limit for innodb buffer pool size of 4G. My computer has 16G RAM. So if I want to make full use of resources, should ...
-3
votes
1answer
59 views

How to organise huge mysql database for emails and search through it

I wanted to ask you for your opinion, what would be the ultimate and the best way to store million of emails and search on them, using MySQL. So first the product: You have tons of emails, which ...
2
votes
2answers
88 views

How to review Database schema?

I am green been software engineer, my team lead give me a task in PostgreSQL database, I need to determine the relationship of the all 405 tables, but the problem is the database doesn't have any ...
8
votes
1answer
147 views

Is it ever ok to not test a feature? [closed]

Is there any point at which you become so familiar with your language/database/system that there is no need to test a new feature/configuration/query/etc. by contained/simulated testing before ...
2
votes
1answer
57 views

DB Schema design : single table with more columns vs multiple tables with fewer colomns

What would be better DB design for a social network website. A single table with more columns and less rows or multiple table with fewer columns but more rows. For example: User can post an update on ...
0
votes
2answers
54 views

Multiple rows v/s multiple columns

My requirement is as follows: An app with internationalization support and starting with EN and FR versions of text. So, I have 2 approaches for data in the table: 1. One column for each language: ...
0
votes
2answers
64 views

Many tables v/s one table with different entities types

My requirement is as follows: App with internationalization and mobile v/s desktop app text support. So, I have came up with below columns in the table which I will certainly need: "CODE" ...
0
votes
1answer
16 views

Optional Relational Filters

Let's say I have the following design: Tables: video_games A list of all known video games video_game_modes A list of game modes for each video game. For example: Game = League of Legends Game ...
3
votes
2answers
95 views

Best design for a tree-like structure database

I'm designing a database in MS Access which is used for tracking the locations of a variety of chemical solutions, made in batches. Each batch can be split (aliquoted) practically indefinitely. That ...
0
votes
0answers
21 views

Database structure for sport venue booking

I'm building a system that allows booking of slots to play games at any sport venue. Now, different sporting venues have different schedules. So I've thought of a plan that allows multiple venues to ...
1
vote
0answers
65 views

What is the best design for dynamic assignment of locations for variable sized grid boxes to occupy?

I want to build a database that will have limited single locations to put boxes. It's divided by lanes and bays. Imagine a 4x4 grid, totally 16 slots, every time I query the table, I want to return ...
3
votes
1answer
71 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
61 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
58 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
169 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
27 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
54 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
31 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
46 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
56 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 ...
0
votes
0answers
26 views

Real time reporting on Mongo

I have my transactional data store as MongoDB cluster on which I need to setup real time reporting(may be a lag of 2 hours will suffice) Data size - 300GB with 20,000,000 rows and 40 columns and ...
1
vote
1answer
39 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
196 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
45 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
14 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
36 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
30 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
28 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
78 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
25 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
72 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
54 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
76 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
30 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
113 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
41 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
101 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
57 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
82 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
44 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
76 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
73 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
53 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
51 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 ...