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)

1
vote
1answer
43 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
87 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
46 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
74 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
54 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
52 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
86 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
40 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
80 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
151 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
46 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
36 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
43 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
49 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
35 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
49 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
26 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
77 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 ...
0
votes
1answer
59 views

How to store a version sequence per product

I need to create a table like the following one: +----+---------+---------+ | Id | Product | Version | +----+---------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 ...
0
votes
0answers
12 views

When to use tags vs binary fields in a constituent management system?

I have a table of Constituents and I want to track yes or no qualities like: are they a teacher? are they an elected official? are they philanthropists? There will be less than a dozen of these ...
0
votes
1answer
45 views

PizzaShop - DB - Normalized Schema

I am trying to make a simple DB in Access for a theoretical pizza shop(the db is for an assignment). What I am trying to do is link one type of pizza to one or more types of toppings. I just wanted a ...
0
votes
0answers
103 views

Ideas for Database schema for Automotive web app

For the last couple of days I have been searching all over Google for database schema for my automotive web application [like a marketplace of sort] (in Laravel 5 PHP). I have tried many models some ...
0
votes
0answers
85 views

MySql Innodb Database Slow INSERT Query to Remote Server

I have searched thoroughly on how to improve general performance of my database which is hosted on Hostgator. I have mostly come across performance tuning strategies which are catered to only local or ...
-1
votes
2answers
205 views

Double entry bookkeeping database design

I'm doing an accounting software and I need to enforce double entry bookkeeping system and I have the classical problem of 1 row per transaction versus 2 rows. Lets take an example and see how it ...
0
votes
0answers
40 views

Post categories model for a user submission wedbsite

I'm building an user submission site where users can make posts to content_types: Blog, Movies, Photos, Forums. Since these content types only have slight variances I would like to keep them in one ...
0
votes
1answer
28 views

Working on a blog creation website; should I be creating a new table for each blog, or keep everything on the same table?

So I'm working on this blog creation website as a side project. I don't actually expect it to become anything, but I want to make it as efficient as possible, even if just for practice. The idea is ...
3
votes
2answers
101 views

Split database to fit on SSDs

I have a database that's about 3TB in size and want to upgrade to SSDs. The SSDs I found that are reasonably priced are only 512GB so I'll need to purchase multiple drives. What are some ways to ...
1
vote
3answers
254 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
0answers
22 views

Calculate Num_Index_Pages for sizing design

I don't understand Step2 #8 Calculate the number of number of non-leaf pages in the index. I am not sure I understand the "Level". I have Num_Leaf_Pages=508 & Index_Rows_Per_Page=476 I don't ...
3
votes
2answers
67 views

Why is vertical scaling expensive?

As you can find here Best Practices For Horizontal Application Scaling, vertical scaling is much more expensive than horizontal scaling: vertical scaling cost increase exponentially whereas with ...
0
votes
0answers
19 views

IBM Data stage compiler error

I have installed InfoSphere Datastage 9.1.2 on Windows Server 2008 R2, and also installed C++, but when I try to compile a job show error in transform stage.
0
votes
0answers
35 views

Mysql - too many Deletion - Insertion

I'm creating crons for my OLAP sysetm, I have a cron that deletes the records from the summery table first and then insert the records in it again, Say Deleting 1 month records, and ...
0
votes
3answers
90 views

Which of these database designs is normalized?

I have two possible database designs. A) a many-to-many relationship between Branches and FiscalYears via a dedicated join table FiscalYearsBranches B) two (or more) many-to-many relationships ...
1
vote
0answers
28 views

When to include and exclude products from a webshop database? [closed]

I'm working for a company that runs a webshop and integrates around 15-20 different merchants. In the database schema for the actual website/webshop (Rails with postgres SQL), products are only ...
2
votes
1answer
97 views

How to write a query, with multiple foreign key in mysql

I am working on a simple PHP and MySQL college level clinic management system. There 4 main table which relation with foreign key. I'm little bit confusing multiple foreign key in table .. what would ...
0
votes
0answers
51 views

Design Payment System - Best way to track different payment types and reasons

I am designing a system to track incoming payments from external entities. Payments will be stored on tblPayment and take place in different ways, to mention some: 1) cash 2) cheque 3) credit ...
0
votes
1answer
40 views

Options for structuring multiple associations to a record

This is 101 level stuff and I promise to read a book later, but in the meantime, given the following three tables: Product --------- ProductId (uniqueidentifier) User --------- UserId ...
1
vote
1answer
35 views

Database table data with different set of values

I am trying to create a database table for my project but I stuck in this type of data. The purpose is the admin can add, modify or insert types of questions and choices example: Do you like ...
2
votes
1answer
58 views

10000 tenants and Muti-Tenant Data Architecture performance?

We are trying to decide on a database design for a new web application. We expect to have close to 10,000 tenants and would like to keep their data in separate databases if it makes sense to do so. ...
0
votes
2answers
66 views

Database Design - Cardinality

I am currently studying for my MTA Database Fundamentals cert, and I came across this question during my studies. However, the answer doesn't make any sense to me. Here is the question/answer: It ...
0
votes
1answer
79 views

Can I have more than one relationship between two tables? [closed]

I want to make database for report maintenance. I have 3 tables: employees header_maintenance_report detail_maintenance_report employees table I have employee_id and then in the ...
1
vote
1answer
47 views

What is the best way to represent an Team and player relationship in ERD

I am working on designing an ER diagram for a football system, i stopped in the relationship between the player and team, as we know the player plays for only one team and team have many players, so ...
0
votes
0answers
39 views

Career Database Design

I am new in cakephp, I want to develop a simple career website, I have design the database, but I would like to show it to professionals and know if the structure is ok Jobs - id - job_title - ...