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)

4
votes
1answer
186 views

How to model complex systems like networks in Postgres

I need help with modelling of a network involving switches (24 port, 48 port, POE, and a combination of those) and edge devices (computers, IP cameras, telephones). From an object oriented ...
1
vote
0answers
39 views

Which database technologies to use? [closed]

We are a small construction company that currently has a very old, outdated database system that has been written over the past 20 years in Visual FoxPro. I have now been designated with the task of ...
2
votes
0answers
11 views

Alternate in access database [migrated]

I want to check whether a row exists or not before inserting a new one in Access 2007. I have the following working query in SQL Server but I'm having trouble converting it to Access. update category ...
0
votes
1answer
24 views

Are cut and paste of records from a table to another a correct pratice?

I am working on an app that does task dispatching. A user inputs a task with a start date and end date. The app gives the user a list of workers available and chooses to offer the task to some of ...
2
votes
0answers
34 views

How to implement search for world-wide Wikipedia/OSM places with alternative-names

I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages. Thus, if you search for ...
1
vote
1answer
44 views

Database design for a car dealers website

I am creating a new website for all the car dealers in my region to show their cars on and i need your opinion on the database I have created because I have some doubts. For instance, is it best to ...
0
votes
0answers
38 views

Does MySQL InnoDB compression combined with Windows compression save more space?

We have a very large MySQL InnoDB database, compressed using the following syntax at the end of the table declaration: ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 We also have windows ...
4
votes
1answer
39 views

Do I relate entities that are indirectly related when creating a student-course ERD?

I'm trying to create an ERD for a tutor center, this is what I understood from their operations: There is the tutor center director, then under her there are sections/departments. It, math/calculus, ...
0
votes
1answer
71 views

MySQL Circular Replication

I have this scenario where I have 4 MySQL servers (A,B,C,D), as this: Server (A) is always online, and serves as the main website. Servers (B,C and D) are online on random periods of the day, and ...
1
vote
0answers
25 views

Storing resource information efficiently

I'm looking for any advice on how to efficiently store some resourcing data for our app. For each project in our system we need to store the resources required and this comes in the following form: ...
3
votes
1answer
27 views

Inventory management in Rails

I'm creating a inventory management app in rails. I have an User Table and a Recipe table, there is also some tables with types of material (pens, sharpies, paper, and so on). Because there is a lot ...
2
votes
0answers
21 views

Is it a good way to use an auto increment integer field as the primary key? [duplicate]

In database design, is it a good way to use an auto-increment integer field as the primary key? For Example, I have a tenant table, tenant_id is unique. In this case, do I need a auto_increment key? ...
3
votes
3answers
93 views

What architecture is best to store a large 3 dimensional array in MySQL?

I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy: { x, y, ...
5
votes
2answers
147 views

Storing a Billing Address Best Practice in Orders Table

Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table. What I'm looking for is how I can set up my ...
1
vote
2answers
31 views

How to set-up a distributed data storage system for The Real Junkfood Project

Context The Real Junkfood Project consists of a network of pay as you feel (PAYF) cafes. We need to log the data from each of ~100 cafes, generating perhaps 50 rows each par day. I estimate that this ...
3
votes
0answers
101 views

Which is more efficient b+ tree or search (binary or linear)?

SELECT A FROM T WHERE M > 1000 AND M < 5000; I am unable to judge which of the below is best suited for the above query: Linear search Binary search on M B+-tree with index on M B+-tree with ...
2
votes
4answers
93 views

Normalization for postal addresses in US (address, county, city, state, zipcode)?

I've been trying to understand for the last few years which way is good for storing addresses. I've been getting "normalize all the way" but also "denormalize as much as you can" and I just cannot get ...
2
votes
0answers
31 views

Design Question - Stock and OnSale Table

Background : I'm designing a solution for a repair toy association. Since they have office - I'll use Access. Plot : Stock will store items - I choose this primary key (Item_Id,Record_date) ...
0
votes
1answer
55 views

TOTAL column or VIEW that computes?

I've got a couple of tables to track volunteer workers at our non-profit. One stores Volunteers and tracks information about a volunteer such as the person's name, a contact number, primary location ...
4
votes
1answer
61 views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
1
vote
1answer
47 views

Database design for 2 1-to-many?

It's the first time here so I don't know if it's the right place to ask.. I'm not good in database design, just learning, so I'd like some advice if possible. The case: I'm building a really small ...
0
votes
2answers
21 views

MySQL - replicate some tables between transaction and report database

I have two different database (on two different hosts). One database is transactional and another database is reporting. Let us say, it stores information about departments and their employees. ...
1
vote
1answer
30 views

SQL 2012 insert data into a table from another table and custom columns

I'm trying to insert data from one table into another table and I also have 4 columns that are not in the source table but need to be populated in the target table. Here is the Insert statement SET ...
3
votes
1answer
120 views

Supertypes and subtypes

I am trying to design a database that has supertype/subtypes. I have the following users: - basic user - business user - administrator (super user). All three users share a common table (users) ...
2
votes
0answers
39 views

Better performance to have nullable columns or an extra table? [duplicate]

There are many ways to get rid of nulls in tables, and I have heard of various reasons to get rid of them, but can anyone show me some real numbers as to how your database might perform differently ...
1
vote
2answers
178 views

Avoid foreign keys on an 800-GB database?

This question is for the experienced developers out there. My friend recently told me that his employer has a legacy code base whose MySQL database has now grown to around 800 GB in size. What's ...
1
vote
3answers
70 views

How to prevent redundant data that is hard to detect, e.g. John Smith

I have a database table to store technicians (employees) which is structured like this: | firstName | lastName | phone | alternatePhone | email | canadaTravel | canDrive | notes | In this table, ...
3
votes
2answers
45 views

Bitemporal Table with changing schema

I have to design a database covering business assumptions using MS SQL-Server 2008 R2. The current data of the tables will be exported to text files and used as input for a certain application. I ...
3
votes
3answers
114 views

Proper way to store a value that could be multiple different types

I have an Answers table and a Questions table. The Answers table has a value, but depending on the question, this value could be a bit, nvarchar, or number (so far.) The Question has a notion of what ...
1
vote
1answer
28 views

Update a unique column with an unknown number of values

I have a table with three columns, ID, a foreign key field and a text field (unique). The user should be able to update all text fields where the FK has a certain value. The problem is that the user ...
1
vote
0answers
18 views

Database sharding, denormalization and synchronization

In sharding, it is advisable to follow a share nothing, shard everything approach. Essentially, this translates to: data that are to be used together are to be stored together (i.e. in a single ...
0
votes
0answers
8 views

Where to put a 0..1 fk column when already having 0..n fk’s on one side and 0..1 on the other

I have a transaction table that represents user’s credit transactions and two tables containing different entities, but both interacting with the transaction table. Say, payment_with_state and ...
2
votes
1answer
119 views

Foreign Key Constraint over two tables

I am trying to put the following entity-model into practice: We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in ...
0
votes
2answers
111 views

What is the best way to find out the sum of a field from a table?

I am making a accounting software. I have a table debit_credit that contains double entry accounting data.Table is: debit_credit_id | tran_id | ledger_head_id | debit | credit | dr_cr | commission | ...
1
vote
1answer
48 views

Count childs of parent with sql or save in column?

In postgres I have a threaded setup where parent id has many childs and childs can have childs. What is the most efficient way to get the count with a sql statement similar to this: WITH RECURSIVE ...
1
vote
1answer
32 views

Real-world scenarios for table with DENY SELECT and GRANT [INSERT/UPDATE/DELETE] [closed]

What is real-world scenarios for this type of permissions? May be it is generally not valid, and made by mistake? Should this kind of permissions be avoided (or prohibited)?
2
votes
0answers
39 views

How To Manage Random Data Without Repitition?

We have a database design problem of a Learning System. Please can somebody help us ? These are the requirements : A person can register for a module which has "n" number of questions. The person ...
2
votes
0answers
58 views

How to store data where various subcategories have specific columns?

I'm new to database designing. I'll use MySQL and PHP. I'm designing a database in which I have a lot of categories and subcategories like given below: Product Table p_id p_title category_id ...
2
votes
1answer
54 views

Migrate existing SQL Server database file to smaller files

I have a database that is about 1TB in size. It's currently on a 2TB hard drive and I would like to replace it with a few 512GB SSD. I need to split the existing database file (.mdf) into multiple ...
3
votes
0answers
32 views

View of structurally same tables [closed]

Let's say I have two schemas (HR and Finance for example), in each schema I have a table with information about document types this department works with. Tables have the same structure: HR.doc_type ...
2
votes
2answers
56 views

Three-sided many-to-many relationship design

I have 3 entity that have a relation to each other. My entities are: OPTION PLAN PRICE Relationships: PLAN and OPTION have a many-to-many relationship Map of PLAN and OPTION has a many-to-many ...
1
vote
0answers
24 views

How do you output field changes in mysql the most efficiently?

I have a rather large employee feed coming in and each employee has about 50 fields. I take the employee data and add on a bunch of other stuff and this gets outputted to one of our vendors. Most ...
1
vote
1answer
59 views

Is my shopping list database schema good enough?

I'm wondering if my schema is correct? It's intended to hold shopping list items for each user, a user belongs to a household. Items will be added to the household. The stocklist will to hold the ...
1
vote
2answers
42 views

Database design - need someone to guide me

Im probably doing this wrong, but here it goes. Im trying to build something like very basic CRM. I know there are plenty of them, but I want something to learn actually. Requirements: Some ...
4
votes
2answers
50 views

Best db setup to accomplish yes/no list of clients for daily calls

I will preface this by saying I am a relative novice to Access but I have managed to develop some basic databases. I have some working knowledge of expressions and macros but VBA is still a bit of a ...
3
votes
1answer
66 views

Can jsonb do fast search by *value* or even a partial value? Other types?

I have a person table where arbitrary data must be inserted. Since I'm using Postgres 9.4, jsonb appears to be the right choice. Example data: id: 1, name: "Joe Doe", preferences: { color: "red" , ...
5
votes
3answers
163 views

Why would I need a third table in a one-to-many relationship?

Good day, Our db professor at the univeristy always said that one-to-many relationships with (0, M) in one side MUST have a third table to relate them. I did not ask him back then and now I cannot, ...
0
votes
2answers
34 views

Multiple types of parts with multiple types of information (unspecified amount) [duplicate]

I'm having trouble designing a database for a project I was working on recently. It's to do with a garage management system. So I've realised I need to store multiple types of parts (Tyres, engines, ...
2
votes
4answers
174 views

Finding all date ranges that overlap a target date range

Say I wish to store when staff are on holiday (FromDate, ToDate) and then I wish to find all staff that are on holiday between two given dates (QFromDate, QToDate). Now assume that I have many such ...
8
votes
3answers
188 views

Is it reasonable to mark all columns but one as primary key?

I have a table representing movies. The fields are: id (PK), title, genre, runtime, released_in, tags, origin, downloads. My database cannot be polluted by duplicated rows, so I want to enforce ...