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
31 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 ...
2
votes
0answers
20 views

View of structurally same tables [on hold]

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 ...
1
vote
1answer
27 views

Three-sided many-to-many relationship design

I have 3 table that have a relation to each other. My tables are: OPTION PLAN PRICE Relationships: PLAN and OPTION has is a many-to-many relationship OPTION and PLAN has many-to-many relation ...
1
vote
0answers
20 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
36 views

Is my shopping list database schema good enough?

So Im wondering if my schema is correct? It's basically suppose to hold shopping list items for each user, a user belongs to a household. Items will be added to the household. The stocklist is ...
-1
votes
0answers
43 views

Design for fast database query over millions of rows [on hold]

I want to query in a database like : Let us consider a student table with the following attributes: (Id, Age, CGPA, Address, Gender). Our table contains record for all the student in the world (so my ...
1
vote
1answer
28 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 ...
3
votes
2answers
46 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
32 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" , ...
0
votes
1answer
23 views

Tools Required to have online database (ms access) [on hold]

Currently i doing a project for college on databases. the scenario is this. I have a webpage with a form asking for basic details from a user. i want the information that is submitted to be ...
4
votes
3answers
118 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
31 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
101 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 ...
7
votes
3answers
169 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 ...
1
vote
0answers
5 views

Datastage connector error

This is a related with IBM datastage 9.1, when I try to test oracle connector. Show error massage like as a below "Error loading connector library ccora11g.dll %1 is not valid win32 application", PLs ...
0
votes
0answers
21 views

database polymorphic associations: design for foreign key point to itself

I have a database design that used to solve polymorphic assocations problem. Here is my schema: Post(PostID, PostType) Question(QuestionID, QuestionTitle, QuestionContent) // QuestionID references ...
5
votes
1answer
35 views

PostgreSQL and table inheritance

I'm not sure if this is the right place for this, so please feel free to tell me if it is off-topic or there is a better place to post. I am working on a fantasy sports based project, and the number ...
0
votes
1answer
28 views

Should we store sum of values in a column, or store in memory database

I have a bunch of questions and answers and total votes of those questions and answers. My requirement that I always show how many votes for a question or a answer (and user doesn't often click to who ...
1
vote
1answer
16 views

Tables design for conversation

I want to create simple conversation module on my site. So I need a list of users who had conversation with current user. I need to show amount of new messages in each conversation. I think about next ...
1
vote
0answers
21 views

MySQL/Eloquent belongsToMany relation - two tables same pivot table allowed? [migrated]

Is it allowed to have two tables, each with a belongsToMany() relation to each other, using the same pivot table? Here is my User table: And here is my Org table: And here is the pivot table: ...
2
votes
2answers
44 views

Database design appoach confirmation

I am working for a mortgage company in migrating a legacy application to new application for managing claims, means lender fore close a loan and request for a claim to get its funds from government or ...
0
votes
1answer
47 views

Using Year Ranges vs Row for Each Year

I'm working on a database in SQL Server for a project. My background is in data analysis and although I'm familiar with database design I don't have a lot of experience. The project revolves around ...
0
votes
0answers
24 views

Help for design DB, avoiding circular relationships

Help for design DB, I have user, order, scratch_card and transaction tables. When user place an order, one record will add to order and another one to transaction as withdraw and when user recharge ...
0
votes
1answer
16 views

SELinux is preventing pg_ctl from read access on the file postgresql.conf

I am trying to setup a master-slave PostgreSQL 9.4 database on Fedora 22, with the slave database's PGDATA being different from the default /var/lib/pgsql/data/ directory. I wrote this to ...
2
votes
0answers
26 views

Distinguishing Records with Related Fields

I'm a newer user (obviously) and would greatly appreciate question writing suggestions as well as answer suggestions: I have a lot of interrelated tables which largely depend on my Products table, ...
1
vote
0answers
5 views

Automatically set table name prefix according to other field in PowerDesigner

I'm working with PowerDesigner 16.5 and I have a PDM generated out of its corresponding LDM. The table names are inherited from the LDM and I'm fine with them, but I would like to generate the DDL ...
1
vote
1answer
45 views

Sharding VS Replication

To improve query response will it be better to shard the data or replicate existing shards for faster response. In case of sharding the data might be nicely distributed and hence the queries. In ...
1
vote
0answers
31 views

Design of user database and basic authentication process [closed]

I want to know if somebody can give me very briefly information describing the design of a user database and the basic authentication process please? Thank you
0
votes
0answers
10 views

SSAS bridge table with Slowly changing dimensions

I am designing a datamart using the basic principles of Kimball for use in SSAS. I have a many many to many attribute relationships (multi-valued dimension objects), however I have a few questions: ...
1
vote
1answer
24 views

Category entity relationship / subtype relationships design

I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to ...
0
votes
0answers
33 views

Versioning database tables

I need to store all the changes made for almost every table in a meta-data database. I am thinking about storing every previous version in a JSON array or XML in the table. Like: CREATE TABLE ...
3
votes
1answer
22 views

Stored procedure deployment - push or pull into database? [closed]

Using stored procedures as part of an ETL process to populate tables in another database, which method would be considered a best practice (if any) in terms of deployment, maintenance, visibility and ...
1
vote
1answer
64 views

Items and their tags

This is my table design: CREATE TABLE IF NOT EXISTS `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `description` mediumtext NOT NULL, ...
0
votes
2answers
46 views

How to export Oracle database without data

I want export my Oracle database with its structure such as tables, schema, users, tablespaces, datafiles and everything but without the contained data.
0
votes
0answers
32 views

Chicken and egg problem with JOIN tables and memberships

So I'm having a problem where users are able to access certain models that depend on a join model that doesn't exist at the time of the access. Quick overview of the use case: Basic school ...
1
vote
3answers
58 views

SSIS: How to check if a record DOESN'T exist in flat file but exists on the database

I am working on preparing an SSIS job where I am importing a .CVSV file to OLE DB destination (sql database). We are going to get these files on daily basis. The .CSV file contains records of doctors. ...
1
vote
1answer
63 views

What is a suitable,stable, high performing database for Large Financial Time Series Data? [closed]

I realise this question may have be answered in some form but I would like to gather the thoughts and opinions of actual users with experience in this field of Database design and development. I have ...
1
vote
0answers
48 views

Ticket booking engine

I am going to build and flight ticket booking system. My client has own planes. Under is basic concept of engine. Admin will add flights. Passengers,agents,franchises or admin will book the tickets. ...
3
votes
1answer
44 views

Schema for scarce goods

I have following objects: (Rooms) which are (available in a certain period of time [in days]). During the time of availability they can be (booked) by (users). There are basically these four tables. I ...
4
votes
3answers
162 views

MySql Invoice and Subscription Schema

I have an ecommerce db built and I'm adding subscriptions. See the simplified structure below. orders id, customer_id order_items id, order_id, product_id invoices id, order_id subscriptions id, ...
0
votes
0answers
28 views

Looking for feedback on a design for a simple task manager

I've got tired of using notepad / excel to manage my tasks at work. Since tasks may sometimes contain sensitive data, my boss will not allow us to use an online task management system, so I've decided ...
4
votes
1answer
68 views

How to model “Courses prerequisites” for a university?

I am trying to model a courses structure and dependencies of a university. For example, in the Computer Engineering department you need to do Data Structures before doing Artificial Intelligence. But ...
1
vote
1answer
42 views

DB design for employee working hours constraints [closed]

I have a simple employee schedule database (I use PostgreSQL): employees: - id - name - email shifts: - start - stop - employee_id Now there is need to apply constraint for working hours. ...
1
vote
2answers
31 views

Include logical key on each table vs. join

I'm designing a database that is used to track an inventory item through its lifecycle. I'm currently relating my tables using the previous lifecycle event's ID (for example, a shipment contains the ...
0
votes
1answer
42 views

How to get last messge between two friends in the messge table having id in descending order?

Hi friends help me to solve this problem ," display all last messages of a user from all his/her friends" My sample message table is --------------------------------------------------- | id | msg ...
2
votes
1answer
67 views

Personal finance database design

I'm making a personal finance management application in PHP and MySQL, for learning myself some more PHP and MySQL. This system is currently using 4 tables: users, this table holds all the user ...
7
votes
3answers
110 views

Why NOT partition?

When would one NOT want to partition a database? (thinking MySQL partitioning) In my case I'll be starting with a couple millions rows, it should grow from there. Primary key on a character field ...
2
votes
1answer
67 views

Storing created/retired dates for a large amount of different entities

In the db I'm working on now almost every entity has these 4 columns: CreatedDate CreatedBy RetiredDate RetiredBy Usually this is used for logging purposes, and for some of the entities the ...
0
votes
0answers
36 views

What happens to the database structure when i add a nonclustered index

Our team is working for a while now with Azure SQL. After a lot of trying , we managed to add a sync group between the main DB and the backup one. Of course, this means that for every change in the ...
0
votes
0answers
28 views

MySQL Db design: Optional data in separate table or as NULL values in original table

I am having following problem: User fills out the form, which consists of two sets of fields - header (basic information about biological observation) and detail (measurements and advanced ...