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
1answer
177 views

Need Advice on Normalizing Database

I have a group of database tables that lack normalization. They all have a similar primary key called 'doc_index', which is either a PK by itself or part a composite PK. As you can see below, there ...
2
votes
1answer
32 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
1answer
546 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
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 ...
4
votes
1answer
148 views

Is this a sane model for a network provisioning system?

I'm sketching out a model for a network setup, where I have the following conditions: There are multiple nodes which contains a number of switches each. Each switch has multiple services, which may ...
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
3answers
291 views

Table Design for Student Attendance hourly for each day

I have created a table (attendance_details) for storing every day attendance of Students in MySQL. Need to capture attendance for hourly (1-8hrs) every day. I have the student details in separate ...
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
2answers
5k views

Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu) [on hold]

Please suggest a tool to reverse engineer a PostgreSQL DB into an entity-relationship-diagram (ERD) or equivalent ?? Ideally, I'm looking for a simple ERD or UML class diagram in a format that is ...
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 ...
4
votes
3answers
119 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, ...
-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 ...
0
votes
1answer
269 views

Allow access to enter data through forms, but not full database access

Using Access 2013 or Sharepoint 2013 is it possible to give someone access to data entry forms that work with a back end database whether Access database or Sharepoint SQL. But from everything I've ...
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
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" , ...
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 ...
12
votes
3answers
1k views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their ...
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
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
1answer
161 views

Rich text: VARCHAR, TEXT or BLOB in MySQL

Question: In my web application (php/MySQL) I will be saving rich text. This may be anything from a couple words to several pages length essay. I’m sure I could get away with using VARCHAR, TEXT or ...
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 ...
1
vote
3answers
110 views

Prepopulate database with empty / null values [mysql]

What would be the best way to pre-populate a database with 100 empty fields and then begin the Auto_Increment? What I mean to say, I want to reserve the first 100 userfields (ie UIDs which are ...
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. ...
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 ...
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, ...
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
3answers
246 views

How to store mp3 files in MySQL database using phpMyAdmin?

I am new to databases, but I want a database for my site to store mp3 files in it, so later I play them on my site by using jplayer. They say there are two ways to store data (in this case mp3 ...
1
vote
1answer
472 views

Multiple product attribute inventory database design

I have a database to build based on inventory for my shop. This is the current data that I have put into tables. This is the package: This is the product attribute: This is the table ...
0
votes
0answers
75 views
+50

MySQL log tables design

I have to make 25 modification historic tables from 26 tables. I need a design for each table that could grow more than 75,000,000 of rows. I use InnoDB for 25 tables and 1 with MyISAM. I have one ...
1
vote
1answer
391 views

CRM Address table and it relation

Hi I want to ask about I have this DBD for my CRM I am trying to have the addresss in one table(customer and Employee Address) and both customerID and Employee ID are guid I have in address table FK ...
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 ...
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 ...
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 ...
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 ...
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, ...
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
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
1answer
335 views

Database design for a table with both bulk insert and single row insert

I have to design a table where records will be inserted in two ways. Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records ...
4
votes
2answers
318 views

What is Transaction Path Analysis?

I came across the term Transaction Path Analysis which I am not familiar with. A Google search resulted in only a few results, most of which seem to assume everyone knows what it means. From these my ...
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 ...
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
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
194 views

Daily snapshot fact table

I have a list of products per costumer. Costumers can delete or add products to their list. I want to know in each day what is the list of products (and count) by costumer. My first approach is to ...
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
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 ...
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 ...