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 (2)

1
vote
0answers
109 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
670 views

which role-permission database design?

I want to manage roles and permissions. Most of designs on web look like this tables: Users Roles UserRoles Permissions RolePermissions Here, what is permissions? I am thinking for such a design ...
1
vote
0answers
273 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
238 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
121 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
567 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
88 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
279 views

PeopleSoft or Oracle App Designer Tool Alternative

We are using PeopleSoft app designer tool to manage and create projects, database objects - tables, columns, views etc in Oracle database. This tool has limitation that we cannot create tables/columns ...
1
vote
0answers
116 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
234 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
19 views

Can Graph Databases be used for an Availability and Scheduled Calendar based application?

Let's say you run a Barber Shop. You have 1 chair available and you want a Calendar to show up on your website that shows all the available time slots, so people can self book a hair cut. In my mind, ...
0
votes
0answers
14 views

How to design a demographics database?

I have a bunch of stores. Each store has the same properties (ethnicity, marital status, income, and so on). Each of those properties has subproperties like (white, black, asian for ethnicity and ...
0
votes
0answers
24 views

identifying the functional dependencies

I have been reading up on database design and I am having some difficulty constructing functional dependency diagrams from tables. I have the following table. Each salesperson is identified by a ...
0
votes
0answers
19 views

why this FD make 3NF?

Question like this: Consider the relation scheme ABCDEF satisfying the following functional dependencies: BC --> ADEF CD --> B E --> D BC is a key and designated as primary. Explain why the ...
0
votes
0answers
45 views

Table design for optimum performance

For example let's say I have to create a table called Employee with columns EmployeeID, Name, DOB, Address, DepartmentID[FK]. So I create the table as follows with a primary key and a nonclustered ...
0
votes
0answers
23 views

Advice on table structure change

I am in the process of converting an Access application that stores and reports evaluations to being web-based using php and mysql. I have converted the structure and data over from Access and I have ...
0
votes
0answers
25 views

Calculate Quantity On Hand By Location

How can I calculate quantity on hand by location and validate the proposed location quantities against inventory transaction? Currently, I do not track location. I calculate total QoH via the ...
0
votes
0answers
26 views

Looking for a recipe to merge multiple rows to single row

I have a aggregation table that is written by multiple processors (around 20). Each row has a composite key - location-id, campaign-id,... and aggregate stats about it. I need to merge the ...
0
votes
0answers
36 views

Should I create a Factless Fact Table or Denormalise my table

I am creating a data warehouse from a store database and I have a question regarding the design of my dimensions and facts. In the store database a table exists for Person, Person_Address and ...
0
votes
0answers
34 views

Using triggers or php code

I have a table called Salons, which has over 15k Salon names. I have another table which takes count of the pageviews of each salon. My question is that if I want to update reputation of the Salons ...
0
votes
0answers
10 views

How to apply updates in a “Fan out on write” architecture?

If I copy certain writes to different tables for faster read performance on certain queries(fan out on write), how should I design updates? Since the data is spread over different tables, I'm trying ...
0
votes
0answers
15 views

calculating Canonical covers

Given F = {AB -> C, A -> BC, B-> A}, compute Fc AB-> C is A extra? {B->C, A-> BC, B->A} 1st possible Fc = {A->BC, B->AC} AB-> C is B extra? {A->C, A->BC, B->A) 2nd possible Fc = {A->BC, ...
0
votes
0answers
27 views

Tables with same schema in DB for different sources. Bad pattern?

The main purpose of the system being developed is to collect data from different sources, process and standardize it, and then add it to the DB. After information from all sources is collected it ...
0
votes
0answers
25 views

Billion records mysql performance with large result sets

I'm currently developing an application which will have several mysql tables where 3 of them have very large amounts of records. The thing is that my app will have to sync each account on a hourly ...
0
votes
0answers
16 views

Is database sharding done on a table level or database level?

I'm new to sharding databases, so I'm trying to wrap my head around it. Let's say we have a database that has 3 tables: Customer, Suppliers and Orders. Each table has 10 million records. I want to ...
0
votes
0answers
29 views

Last synchronization time between master and slave (replication)

During replication process where a Master is connected with slave. Some times connection between these two goes down. Any options to get the details of the last synchronization time?
0
votes
0answers
24 views

How to store multiple raster files in postgres / postgis efficiently

This question is partly linked with another question on GIS:stackexchange, but I believe it is has more to do with clever data management. I have a Postgresql server with Postgis extension running ...
0
votes
0answers
25 views

database design - upvotes for various types

In the early stages of an application I'm developing, I had two relatively simple tables - one for content and one for upvotes. The content table's schema is as roughly: data | text id | integer ...
0
votes
0answers
25 views

Entity Schema Design

I'm working on an application that has an "entities" system (built before my time) that models users, customers, contractors, vendors, etc. These are all types of people, but have different needs in ...
0
votes
0answers
18 views

Database & Structure for SaaS reporting software

I'm building a SaaS application to allow users to upload data and perform reporting and analytics on it. The reporting and analytics would be quite simple like grouping and aggregating. This has ...
0
votes
0answers
53 views

How to select column names that contains specific value from other table's column

Suppose I have table items and logs. Both tables have col1, col2, col3, col4... col(n). col1.. col(n) in table items simply specifies key name (col1 = "beef", col2 = "chicken", col3 = "bacon") ...
0
votes
0answers
24 views

Two types of rows with mutual fields and unique fields

I have a question about DB design. (MySQL) In my website, users can leave reviews for Stores and Products. Right now, there's a table for stores and a table for products. Each item ('item' = either ...
0
votes
0answers
17 views

Merge colums from two different databases using phpMyAdmin

Is there a way to: Replace all the data from database1 > table1 > column2 With Data from Database2 > table1 > column2 ONLY under the condition that the ID value in database1 > table1 > column1 ...
0
votes
0answers
14 views

Handle a Search on 2 Big Tables

Hello i want to realize a exchange system with 2 tables. 1 Table for "the user is looking for" 1 Table for "the user has to share" the Tables are very simple id bigint user_id bigint object_id ...
0
votes
0answers
40 views

Multi-Tiered Database Design Issue

I am trying to build the multi-level model as demonstrated. When a user creates a campaign they will select some categories and some metros from tables Category and Metro. Those selections will then ...
0
votes
0answers
32 views

SQL query to fetch results for multiple many to many relationship tables

We are developing a database for storing licensedetails of source code, archives, shareobjects, binary. We parse a detailed build and extract list of source files that make objects, list of objects ...
0
votes
0answers
14 views

Design and queries for analysis of event-sequences

I will have to store sequences of events with certain properties in a database (MariaDB preferably) and I have some issues regarding the design and queries. The crucial part is the analysis of the ...
0
votes
0answers
91 views

Suitable database to store GPS track data

I have a network of vehicles. Each vehicle is equipped with a GPS sensor sending data over 3G to my FTP server. As a first solution to test the sensors, each vehicle sensor sends a .csv file to the ...
0
votes
0answers
43 views

Architecture for huge catalog of products

I need to upgrade my current system of product catalog. For currently I have a 12 000 items in catalog, it's not a lot, but I need to improve them for feature. I have a task to reorganize my database ...
0
votes
0answers
28 views

many to many relationship tables for four or more master tables

We are developing a database for storing licensedetails of source code, archives, shareobjects, binary. We parse a detailed build and extract list of source files that make objects, list of objects ...
0
votes
0answers
305 views

Need Help in DFD Diagram for online hotel booking system

Hi friends, I am creating a data flow diagram for online hostel booking system. not sure if i created a correct diagram or not any help would be appreciated. thanks in advance
0
votes
0answers
55 views

Oracle 11 table design for better performance for reports?

We have a requirement to design a report page. A few factors we have are: number of fields in report is 70 computed fields is going to around 14*8 (based on toggles) a filter is going to have ...
0
votes
0answers
14 views

How to model mappings at various levels

I'm trying to design the database model for my application and I'm finding it difficult to design the mappings for user access to various entities stored in the database. If access was only ...
0
votes
0answers
62 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
0
votes
0answers
36 views

DB design, when to split out into schemas

I am building a storage solution to store important themes/concepts referenced in books. At a high level the system will contain one or more catalogs and each catalog will contain one or more books. ...
0
votes
0answers
13 views

How to test if a model is erroneous?

i am trying to realize why the following model is erroneous(or not). i am taking a database course and while i was studying I've found that model.
0
votes
0answers
31 views

Minimize table joins in design

Probably didn't come up with the best title, but I'll explain what I'm trying to do. I need to be able to track gym members, trainers, workouts, and gym locations. I've created a simple example below. ...
0
votes
0answers
28 views

Optimal layout strategies for Time-Series using Columnar storage

In the context of time-series and specifically columnar storage systems. Question: What are the processes or heuristics that are used to determine how best the columns of contiguous values be grouped ...
0
votes
0answers
28 views

Conditional value lists in MySQL

apologies in advance if the language I use here is insufficiently specific. Please let me know if there's anything that I can clarify. Does MySQL provide a means of creating conditional value lists a ...