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
0answers
98 views

DB Design: Looking for Performance improvement with a Column used in all SQL Queries

I recently got added to a new ASP .NET Project(A web application) .There were recent performance issues with the application, and I am in a team with their current task to Optimize some slow running ...
1
vote
0answers
68 views

Relational or non-relational database for a system to pass objects on?

hopefully this is the right place to ask this kind of question. I am working on a system that focuses on helping people give away things that they don't use anymore. So, an object typically has the ...
1
vote
0answers
201 views

How to handle many columns / variable schema?

I apologize in advance in case this question has been asked already. I'm working on revamping a reporting application used within my company. The requirements are: Support addition of new fields ...
1
vote
0answers
75 views

Identifying functional dependencies

I'm having trouble understanding the logic in use for identifying functional dependancies. Looking at the sample relation below, I understand fd1 - fd3. But when I look at fd4 and fd5, its logic ...
1
vote
0answers
169 views

Conceptual model vs Logical model vs Physical model

I'm doing a work about database, and now I need to show three different images, one image with the conceptual model, other with logical model and other with physical model of a database. I'm looking ...
1
vote
0answers
99 views

Dealing with Multiple User Roles

I'm working on a project based on a CMS, where there is a built-in roles table. However, the CMS allows a user to have one role only using role_id And these are the requirements that struggling me: ...
1
vote
0answers
24 views

Optimize finding the next batch of data from A to be crunched and inserted into B

I'm using PostgreSQL to store data produced by a large distributed computation. There are several stages to this computation; the idiom we've settled on is that there's a table containing the results ...
1
vote
0answers
135 views

“Replicate” Table structures with sp_addscriptexec

In our customers environment is a massive central server (running SQL Server Enterprise 2008R2) and some distributed PCs (running SQL Server Standard 2008R2). The central server has a replication for ...
1
vote
0answers
314 views

Postgresql - How to Convert all Column Names to Lowercase

I have a travel survey with a series of tables (lots and lots of columns) that I exported from MS Access into a postgres database. I've search around a lot for an explanation of how to convert all the ...
1
vote
0answers
21 views

Modeling for inclusion of two different entities

What I Have I am currently hashing out the data model for an application that will utilize MongoDB. In my setup, users or members (as they are called in the schema) will be created and then assigned ...
1
vote
0answers
511 views

How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?

I have created table as post ( post_id int(15) auto_increment primary key , user_id int(11) , post_text varchar(2000) , post_type int(1), ...
1
vote
0answers
23 views

Difference in the workload related to data-amount and search option

A couple of days ago I was asked why it is necessary to use the first normal form also for separation of names. We have a table which stores private customers (Name+Surname;Information) and business ...
1
vote
0answers
1k views

Database design: Normalizing a “(many-to-many)-to-many” relationship

Short version I have to add a fixed number of additional properties to each pair in an existing many-to-many join. Skipping to the diagrams below, which of Options 1-4 is the best way, in terms of ...
1
vote
0answers
36 views

Basic Design Question about one to many in a table

Sorry for the basic question but I am new to all of this! Say I have a table named Runs, and each table has Run: id (primary_key) data (String - path to a data file) status timestamp ... ...
1
vote
0answers
40 views

Second opinion about elimination of possible redundant database column

We currently have a Widgets database table were we track the widgets the company produces. When a widget is produced the widget information is added to this table. This Widgets table contains a Status ...
1
vote
0answers
32 views

A better model for connecting users to different objects, even efter they cease to exist

I've got the privilege to create a small in-house application for managing todo-lists, although I am not really a developer. (I know some PHP and SQL, and got a chance to prove myself). As my ...
1
vote
0answers
129 views

Can somebody check this normalisation workings i think i am missing a step or a dependency somewhere

given these two tables: and: I need to first list them as an un-normalised structure(s) then i need to identify the functional dependencies/primary keys. Once i have done this i must represent ...
1
vote
0answers
162 views

How to join the tables in mysql to get wall messages and comments with name of person ? Only user and his friends messages

I have four tables in my mysql database as shown in the image url provided. I want to select data from the tables without duplication. I want to show messages and comments on wall according to the ...
1
vote
0answers
35 views

Keys of a relation

I got relation: R = { (ABCDEF) , (A B F -> C, B C -> F, F -> A, F -> B, D E -> E, E -> D)} keys of this relation are: {BCE}, {EF} But i dont get it... why? Why not only {EF}.. it got less ...
1
vote
0answers
796 views

SQLite Design: Better to have many tables with few columns or few tables with many columns?

I am struggling to figure out the best design for a Sqlite database that will hold data about Facebook pages so that I can calculate benchmarks for how the average Facebook page performs. I have an ...
1
vote
0answers
149 views

Relation between lossy decomposition and Natural join?

I am confused with the lossy decomposition and natural join. Natural join(with common attribute): only respective common attribute for the resultant table is formed. Lossy: Natural join on tables ...
1
vote
0answers
113 views

Schema for yearly data models

I am developing a school management system. Most data will not be reused after the end of the school year. I am contemplating using schemas to separate data in various school years, however I am ...
1
vote
0answers
93 views

MySQL: What criteria should I base my table column order/arrangement/grouping on?

Does the order, arrangement or grouping of columns with large (such as long text descriptions) and small (such as bit and tinyint) field types have any noticeable impact on MySQL (InnoDB engine) ...
1
vote
0answers
26 views

Optionally free-form reference

I've seen a few instances of entities which have a field which can be either a reference to another entity or free text. You typically represent this in the UI by having a dropdown widget which can ...
1
vote
0answers
143 views

The correct way of representing folders

I'm trying to kind of emulate a folder structure in my application. Now I've thought of two ways to achieve this, but what is the best practice for that? Table: Attachments Columns: Id (PK), ...
1
vote
0answers
341 views

How to store object with property of generic type in DB

In my business domain there exists an entity parameter. Beside other properties which are identical for each parameter, they have values. These values can be of different data types. There might be ...
1
vote
0answers
116 views

Are duplicates ever acceptable in a database

A warehouse system I am working on allows inwards consignments [type = 0] and outwards consignments [type = 1]. The user selects multiple products for each consignment and then multiple warehouse ...
1
vote
0answers
49 views

Where should a default setting field be?

I am creating a database to track occurrences of incidents on buses based on an existing system. Currently the drivers can set a default bus number, which is used to pre-populate the bus number on the ...
1
vote
0answers
386 views

Data modelling: RBAC for small enterprise software

I'm building the core tables for a small, cloud-based enterprise app. Is this data model suitable for a role based authentication system (flat hierarchy of permissions) that allows users to work for ...
1
vote
0answers
250 views

When to split a large table

I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc). However, in my use-case, every single day, the table gets 2160000 new rows. It's also crucial that ...
1
vote
0answers
288 views

Database Design: separating reusable & instance data

I'm developing an app which is basically a collection of checklists that have to be processed. I obviously need a database in which the information has to be stored. I just can't wrap my head around ...
1
vote
0answers
130 views

Real Time data collection - Help select Database

My Scenario: I want to collect data from various sensors[for now I have 600 sensors sending data, but this may have to scale up to 3000 more data sensors, all sensors send real time data] Now I have ...
1
vote
0answers
692 views

How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
1
vote
0answers
105 views

Unable to decompose this relation to BCNF

I have a relation R = { A, B, C, D, E, F, G, H, I } And functional dependencies F ={ ABC -> DE E -> C AB -> F C -> G F -> H H -> IJ F -> B } I am able to do simple BCNF ...
1
vote
0answers
122 views

key value database

I have one object and a lot of data associated with that object, you can think of it as something like facebook timeline where we have user and user has a lot of posts. I cannot store data in JSON ...
1
vote
0answers
266 views

Improving database design for category based site

I'm working on a site that gets about 30k visits a day and it makes use of a lot of queries which uses UNION's over about 45 different tables, these tables generally range from about 15k rows to about ...
0
votes
0answers
9 views

Party Data Model / Universal Data Model Example

I am trying to implement Party Data Model / Universal Data Model in our application which needs to store different relationships between entities (People, Companies). Example of relationships: ...
0
votes
0answers
17 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 ...
0
votes
0answers
37 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.
0
votes
0answers
29 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
0answers
20 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 ...
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 ...
0
votes
0answers
40 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 ...
0
votes
0answers
51 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 ...
0
votes
0answers
11 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 ...
0
votes
0answers
18 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
28 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 ...
0
votes
0answers
29 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 ...
0
votes
0answers
23 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 ...
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 ...