The logical model and/or physical design of a database.

learn more… | top users | synonyms (4)

0
votes
1answer
59 views

Database design advice on a marksheet schema

I am not sure whether this type of question is allowed here, but seeing other questions, i think it is. I am doing a database design of processing exam marks. There is a marksheet for each of the ...
0
votes
1answer
20 views

Handling modifications of fields within a document

I have a basic web app where with the following hierarchy being persisted >User1 >Client1 >ID >Name >Profession >Client2 >ID >Name >Profession &...
4
votes
2answers
132 views

How to design SQL schema that treats historical data as first-class citizen?

I'm currently designing a SQL Server database schema for an application that consists of highly structured "tree" data. So, a table might look like this: id parentId (references id in same table) ...
8
votes
1answer
144 views

Functional dependencies applies to whole database or to a particular relation?

I am new to dbms. I saw a lecture video about functional dependencies. But I am confused a bit that whether functional dependency is a constraint put on whole database or on a particular schema?
1
vote
0answers
164 views

Dynamic Survey DB Design

My problem is that I need to digitize a series of 5 ticksheets/surveys. Each one has a generic structure but are used at different times and have different questions; each one has at least 50-75 ...
2
votes
1answer
123 views

Saving and managing multiple database “states”?

I'm very sorry if this is the wrong place to ask this question. I have been tasked with implementing a system to allow administrators to make changes to data in a database, and save these changes ...
1
vote
1answer
148 views

Designing an activity feed

I'm trying to design an activity feed, but I have a small problem. Here's my current table. activity id user_id status activitytype_id (status, video, photo, etc) created_at location video I didn'...
0
votes
1answer
90 views

Store parameters of various types

I am a (poor, young) scientist working to develop a database to allow a project to scale up. I am experimenting with MySQL and have had very encouraging experiences so far, but I am still novice and ...
3
votes
1answer
824 views

Designing a simple library database

I am currently developing a simple book library system in ms access 2007 and need some help with the database design. I want to register books, borrowers and loans, so I assume that my database could ...
1
vote
1answer
88 views

How to represent the following ternary relationship

The following image shows that each department can be associated with several employees and locations, and each location can be associated with several departments and employees; however, each ...
4
votes
2answers
768 views

Best way to design a database and table to keep records of changes?

I need to setup a history feature on a project to keep track of prior changes. Let's say I have two tables right now: NOTES TABLE (id, userid, submissionid, message) SUBMISSIONS TABLE (id, name, ...
0
votes
1answer
41 views

Can not install a database, can I still learn though?

I am given access to a domain where I can put my code in and it will go public, like in this example. In order to store the data right now, I am using PHP and I write the data to a file. When the page ...
0
votes
2answers
344 views

Mysql big data solutions

I am in charge of a mysql database which is about 12 gb, and it grows pretty quickly (was 3gb a few months ago). I wanted to know what measures i can take when the database grows even larger (lets say ...
3
votes
4answers
3k views

Calculate row size and max row size for a table

Problem: Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate ...
0
votes
1answer
62 views

I have a database design for an E-commerce website. Need to know how to display related product for each specific product

For example: A customer is viewing a product on the website, say a laptop. There's a section on the left where i need to display products from the database that are associated with this particular ...
0
votes
1answer
128 views

Web application for Software as a Service - Shared database or Multiple Databases?

(I am not sure if this is the best place to post this question. If not, please redirect me to the appropriate stack exchange site.) I am looking to deploy a web application for multiple clients. The ...
2
votes
1answer
227 views

A good use case for Postgres JSONB?

I am looking to create an Asset Management System to track assets (laptops/desktops, monitors, keyboards, mice, bags, software keys, etc), basically anything that can be assigned to an employee. My ...
0
votes
1answer
208 views

How to setup sync between MongoDB and C# .NET Application

What is the best practice to synchronize a MongoDB remote database with a C# .NET App that would allow queries to be responded to within the local file system and update itself when there is a change ...
1
vote
1answer
33 views

Database: what design to have a “document table” referencing others tables

I have some tables which can have 0..* documents. How can I design my data model to avoid having a specific document table for each of my tables? Feel free to suggest by editing the data model
3
votes
0answers
216 views

Form builder schema for SQL database?

There are many ways one can envision the solution, from the top of my head: Two table approach: Field table with (name, field_name, type, description, meta) attributes Form table with (name, ...
2
votes
2answers
740 views

Database design for a blogging site

I would like to know if this is a good database for a blog: This is a data model for a blog system that has the following characteristics: Users can register Users can have different roles, eg. ...
4
votes
1answer
178 views

Best database design for website ranking

I am working on a project in which I need to rank websites in two way - Global rank and their rank in a particular country. There are various factors based on which both are calculated. There are ~200 ...
5
votes
2answers
765 views

Conditional Foreign Key Relationship

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
1
vote
2answers
81 views

Duplicate Same Table for Multiple locations

We have an "appointments" table with following schema. appointments(app_id,.....,app_type,location_id) app_type - type of appointment location_id - keeping the location for multiple location setup. ...
1
vote
3answers
67 views

Making sense of these business rules

My scenario is a shop/warehouse and I have a Product table which contains basic product details including it's name, price, quantity on hand etc. I have a Customer table that has the shops customers ...
1
vote
3answers
80 views

Create database design with requirement

I has a small database structure with an Account and Person. I want apply the following requirement: Account have one Person Person do not have necessarily one Account (can be null) When I delete ...
1
vote
1answer
50 views

Using REGEX_LIKE to Implement a Check Constraint

Background I am working on the database design for an application that tracks information about database development projects at my company. The project management office (PMO) assigns a psued-...
6
votes
3answers
476 views

Need help on my first ER diagram

I just started my first Database course online, and I have a homework assignment to create an E-R diagram from a list of specifications. The specs are as follows: The company is organized into ...
4
votes
1answer
153 views

UPDATED: Relational (or NoSQL) Database Design

(Updated this question with more specifics. Decided on a Relational Database, and would love some comments on its design) This database holds assembly instructions data for shipbuilding, later use in ...
1
vote
2answers
66 views

Index planning for many columns with different usage

I've a table with 80 columns and that is a base table for most of the application. Daily load inserts almost 8,000 records and update upto 2,000 records. This table is now having more than 5 million ...
0
votes
1answer
113 views

Linking MS Access 2010 to Outlook Inbox: Including Attachments

I'm trying to set up a MS Access table linked to an Outlook folder. I've been able to use the external data wizard to set up a linked table, but the table seems to only include subject lines and ...
2
votes
0answers
166 views

How do I provide a failsafe for retrieval of data from excel to SQL

So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run. I sometimes have ...
0
votes
1answer
65 views

Database design help required

Need help designing in my database tables. I have a set of table which are being populated on every execution, like wise this executions are user specific if user executes an execution data at that ...
1
vote
1answer
66 views

What kind of database should I use?

I'm designing a new system and I'm wondering what kind of DB should I use for my needs. I have a tree structure of nodes, where each node has historic values. For each historic value it should hold 2 ...
6
votes
1answer
135 views

MySQL database design to sort and filter air traffic data

I am struggling to design the correct database / structure for what I'm trying to do. Let me try and explain the situation. Background We are a group of enthusiasts who monitor air traffic via ADS-...
17
votes
4answers
456 views

Entity relationship problem

I have 4 tables related like this (it's an example): Company: ID Name CNPJ Department: ID Name Code ID_Company Classification: ID Name Code ID_Company Workers: Id Name Code ID_Classification ...
0
votes
0answers
125 views

User is An employee. Role or relation

I've created a database based on a party-model. In short I show you the tables: parties, kind, party_to_party_relations, role, party_roles table parties: CREATE TABLE `parties` ( `id` int(11) NOT ...
0
votes
1answer
52 views

Is it good to save Keywords stored by users in a new DB schema?

My question is more from the schema design and of further performance of the DB. I'm designing a DB schema which will store primarily strings. Actually, users work on these string, as normal mortals, ...
0
votes
3answers
67 views

How to design a Table in which its fields varies on its categories?

I am trying to design a database to post free ads. Eg http://www.gumtree.com.au/p-post-ad.html Scenario: Every ad will have different categories and some categories will have further sub categories. ...
0
votes
1answer
31 views

Slow query - just error count

I deal with slow queries in whole project. I indexed heavily but the results are nowhere. I had a lot of logs (thousands per minute) from SCADA systems and I am trying to count them for each SCADA ...
8
votes
2answers
535 views

NoSQL : What is unstructured data?

we are currently running at the edge of resources with our mssql server based solution. We have now many traditional options regarding the next move to tackle the load: buy faster CPUs and IO ...
1
vote
3answers
147 views

Users vs. People - where to keep the email address?

I'm designing an app similar to Facebook, called Headtome. I want to have users that create public profiles, including email addresses. Then, Mary can choose to message John, and that message goes ...
1
vote
2answers
97 views

Suggestion for enforcing unique constraint in a table where soft delete is implemented [duplicate]

I am using SQL Server 2014 and in my database, I have a Position table which is structured like this: ID int PK Name nvarchar(50) Unique Index IsDeleted bit A ...
3
votes
2answers
116 views

Computed Column Update when Table Value changes

I have environmental sensors collecting some raw data from environmental stations. In addition to the sensors, there is a physical gauge measuring water height (staff gauge). I'm trying to set up the ...
1
vote
1answer
46 views

Is this the correct way to model a relationship between an object which may or may not have another object?

I'm creating a database for a department store and the database has two tables: Items and Coupons. An item, like a TV, can only ever have one coupon. Some items don't have a coupon at a given time. ...
7
votes
3answers
516 views

Implementing versioning system with MySQL

I know this has been asked here and here, but I have the same idea with a different possible implementation and I need some help. Initially I had my blogstories table with this structure: | Column ...
4
votes
2answers
279 views

Potential schema for product failures and repairs

I'm creating a schema for product failures and repairs and would appreciate feedback on the schema. The database is intended to capture all product failures and corresponding repairs. A product can ...
11
votes
3answers
387 views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | ...
4
votes
1answer
94 views

How can I design a database to track each individual's stat changes weekly, in a group?

There is a group of people, and I need to track each person's individual numbers weekly such as their weight lost, steps walked, and bad foods eaten. I plan on implementing this database into ...
0
votes
1answer
42 views

A form including content from two tables

I am working to create a database of all the places or administrative entities of one country. Most work will be about adding new districts. I have to detail which region every district belongs to. ...