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)

7
votes
1answer
259 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
4
votes
1answer
387 views

Database table design question

Firstly, database novice here. I'm trying to create a system for processing Sales Orders into Purchase Orders and then split the Purchase Order into Shipping Containers. The systems needs to be able ...
4
votes
1answer
723 views

Designing a database for a site that stores content from multiple services?

I'm building a site that implements David Allen's Getting Things Done that pulls in your email, Facebook newsfeed, tweets from those you follow on Twitter, and more services are planned. The problem ...
3
votes
1answer
197 views

Removing “duplicates” within a time range

I have a table that contains number plate data with a date and time stamp, collected by a plate-reading CCTV camera at a site entrance. Vehicles stopping at the site entrance will create multiple ...
2
votes
1answer
22 views

Save different account types in a database

I have the following issue. In one application (imagine a messaging/e-mail app on your smartphone), the user enters its accounts and gets information from the server displayed. I need to store the ...
2
votes
1answer
106 views

How to normalize my db schema for a quiz application?

I am putting together a quiz application. (I'm calling each quiz a "challenge") I have a "challenge" table and a "challenge_question" table which is pretty straightforward. challenge table: id ...
2
votes
1answer
278 views

Designing a reservation system schema

I am not sure if this has been asked before, however I am tasked with designing a new reservation system for my organization We have an older system which simply stored the booking in a table, and it ...
2
votes
1answer
183 views

SQL table architecture

I'm looking for a SQL architecture solution for the following problem. The idea is that I have a validation form that contains the client's data. Each client can log in to his account and edit his ...
1
vote
1answer
63 views

Enforce referential integrity between two groups of data

I have two sets of records: A collection of rules. A collection of actions to perform based on those rules. What I'm trying to model is something like this: Multiple rules can be grouped ...
1
vote
1answer
66 views

Optimizing large MySQL SELECT WHERE IN clauses

How can the following SELECT WHERE IN case be optimized? I have a table with over 100 million rows with only 3 columns. The primary key (col1) is a 127 varchar. I am performing a SELECT col1 WHERE ...
1
vote
1answer
22 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
1answer
46 views

Reviewing relational database tables

I am building a database, but I am a little bit confused with my table relationships. I wish someone could help review my table relationships below for me and tell me where I am going wrong. I have ...
1
vote
1answer
35 views

Should a ledger DB store a separate line for each side of a transaction?

I'm working on a database that needs to do manage transfers between several accounts. We are considering using a simple ledger system for this (like what you find in Quicken). I have two concerns ...
1
vote
1answer
93 views

Database Design for NoSQL Fantasy Baseball League

I am trying to create a proper schema to place scraped data into, using a MongoDB database. Currently it looks like Team { id : id, name : String, teamLink : String, username : ...
1
vote
1answer
46 views

Recipe Health risk Database ERD structure

I have these tables: First table(Recipe table), Recipe recipeid(PK,AI) recipename ingredients Second table(Ingredients table), Ingredients ingredientid(PK,AI) ...
1
vote
1answer
149 views

Should I save “document ID” as a String or Number in MongoDB

So I'm importing about a 100,000 documents into mondoDB. MongoDB already generates an Object hash but the data has a documentID that was used to identify records in the previous database. The IDs ...
1
vote
1answer
82 views

Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

Below is an image of hard coded generalization UML logical model of entities. I'd like to implement these classes as tables in a physical data model. Two derived classes/entities (Class2 and ...
1
vote
1answer
128 views

Enforce a constraint problem

This business model is for a case management database. This is closely modeled on the idea of a file folder representing the phase and a sequential checklist representing the stages. A case consists ...
1
vote
1answer
68 views

representation in ms-access

I have a database in microsoft access. I want to know how to look up a singular datum from a reference table giving a dynamic set of values. Here is a representation of what I mean: I have the ...
0
votes
0answers
59 views

Advice on DB Changes

Requirement: I'm required to prefix a couple of strings to the existing one. I.e. The current way, the Accounts been recorded in AccountID field looks as "621230093VN00" Under the new format ...
0
votes
0answers
55 views

Inserting and updating on same table

I am currently working on a database that aims to provide information regarding the amount of time people stay in a company's car-park premises. This is done using a table called "tbl_stay" which has ...
0
votes
0answers
70 views

MySQL: Error 1118 using create table select and barracuda file format

We want to create the equivalent of some materialized views in a read-only slave for really performant reporting. The queries used to generate these tables span 19 tables with a fair number of ...
0
votes
0answers
174 views

MySQL Workbench entity-entity relations and ENUM data type

So I'm creating an EER model diagram using MySQL Workbench. I have a table task_types with only one column task_type_id, where I store a list of different types type A, type B,... That may be updated ...
0
votes
0answers
13 views

Working with a quiz - percentages per item

I am creating a database for a fairly large quiz. It has a few questions that ask you to sort the items by percentages which must equal to 100%. So for example Household Income: $20,000+ 40% ...
0
votes
0answers
28 views

How to find a projection on the functional dependency?

I have below dependencies on attribute PUT: P -> QR T -> WV R -> UV V -> P Now how to find a projection on the above functional dependencies? I am trying to find a article which can ...
0
votes
0answers
90 views

Improving User Registration Design

I have two sources of data. One is an internal list of serial numbers shipped from the warehouse. This data is clean and valid. The second is a customer supplied registration data of serial number ...
0
votes
0answers
46 views

how to design my database for faster reads for my situation

I have a situation where i have two categories. Lets say type A and type B. In my database I have some 50,000 type A entries and 100,000 type B entries as of now. For each entry mentioned above(type ...
0
votes
0answers
62 views

Datamodel design for multiple roles, country access

I am in the process of creating a data model for the below scenario there will be two hierarchies a) dept hierarchy (emp -> team -> dept -> branch) b) sales hierarchy (emp -> marketing manager -> ...
0
votes
0answers
660 views

Database design to track inventory?

Would this be a good starting point for tracking inventory with several warehouses? If yes what is the best approach to sum quantity when displaying stock by warehouse/type/general? mysql structure ...
0
votes
0answers
31 views

how to use information table's result in a select statement

SELECT COLUMN_NAME into columnname_date FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='syslog_data' AND `TABLE_NAME`='syslogs_index_1' AND ordinal_position = forder_date+2; When I ...
0
votes
0answers
64 views

Database design: Modifying an open source database without a lot of changes

I am adding more functionality/features to an open source project. The project is about a forum where each post can be given a specific list of tags. The tags are homogeneous. I.e they are of similar ...
0
votes
0answers
729 views

Multi user messaging database design

I'm designing a database for a new project and I'm looking for some feedback on the design before I start to implement. It basically consists of secure messaging system with those abilities : One ...
0
votes
0answers
36 views

Question on populating from database

I am trying to populate a workout from a list in a database. For example, the Bicep Curl machine has these sets and reps: bicep curl machine sets 1 reps 50 bicep curl machine sets 2 reps 25 ...
0
votes
0answers
59 views

Can I have a Distributed Database in which one database is MariaDB and the other one is MySQL Cluster?

This is a hypothetical question of course, I just want to know, because that's one of the question i'm going to be asked in my presentation and I have a feeling that the answer is "yes" after all my ...
0
votes
0answers
93 views

Import only selected column from tsv into mongodb

Edit I am uploading very big tsv file thats why i dont want to import unnecessary column in mongodb, I know how to upload file into mongodb(just as attachment) as:- mongoimport --db xyztsv--c ...
0
votes
0answers
21 views

Joining data from 2 different servers

It is possible to host my databases on 2 different servers? For example if I store my comments on 'heroku' and store users on some other server it is possible to join the two servers together?
0
votes
0answers
275 views

Trouble converting MySql Database to SQL Server

I have an mysql database that contains 38 tables. I used "SQL Server Migration Assistant for Mysql" tool to convert this database to SQL server Database. I successfully converted 37 tables. Only one ...
0
votes
0answers
561 views

Help with Multi-Restaurant Menu database design

I'm building an Online Food Ordering System with PHP and MYSQL, I need some help on the database design of the Menu. Please look at the design here Is the design good enough? To hold the menu's for ...
0
votes
0answers
61 views

How can I model this problem in a graph database?

I have a project that I'm working on where I extract data from PDFs and map/visualize the relationships between the extracted pieces. Here's an example of my problem: file: 11425646.pdf author: ...
0
votes
0answers
105 views

How implement MySQL unique index with two foreign key that one can be null?

I would know if can be declared in MySQl to create an index constraints in a table with two foreign key that one can be null. For example cannot be repeated a null foreign key with the other foreign ...
0
votes
0answers
135 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 ...
0
votes
0answers
75 views

In regards to GS1 barcode - what size DB field are used?

In regards to GS1 barcode - what size DB field is used or is the entire GS1 typically not stored in database, but rather processed in the application layer and stored in the database as a collection ...
0
votes
0answers
84 views

Database Schema/Structure advice

Well i am reconstructing an already created application and was looking to improve this, especially with in the database structure. I want to start off with probably the biggest section and that is ...
0
votes
0answers
143 views

Ternary Relations three attributes and an association

I have 4 tables: Workers Work Workers Relationship/Work Registration Time I need to have organized the Workers Relationship with Work so you can make a ListBox organized. Where each worker may ...
0
votes
0answers
374 views

Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logs

I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs. All the primary keys are type serial (auto incrementing integer) and all have the name in the ...
0
votes
0answers
113 views

UML class diagramm for database design?

I used to make database designs using a french method called MERIS that uses mainly two diagrams to create the database (MCD and MLD), then i heard about UML and that is used also for databases and ...
0
votes
0answers
265 views

error in creating trigger in phpmyadmin

I have 4 tables: event: event_id(p.k) | uid | circle_id(f.k) activity: uid | performed_activity_id(f.k->event_id) | activity_type_id follow: follower_id | circle_id(f.k) noiticication: ...
0
votes
0answers
171 views

Schema for Google Docs-like sharing

Am trying to design a nicely normalized schema for sharing entities in a database, with good referential integrity. It should work similarly to Google Docs sharing. Here is what I have so far: ...
-1
votes
0answers
21 views

Justfity the relation of Multi Value Dependecy(MVD)

let say i have this database table: This is how i justify the relation has the Multi Value Dependency (MVD) { Book } { Author } | { Edition, Year } as below. "Because the author attached to the ...
-1
votes
0answers
18 views

Minimal db structure for multiple parents and multiple children nesting

I stuck trying to do regular category nesting with noSQL db like parent -> child -> child but in one table. DB engine is parse.com so i got pointer and relation as db relation options. what ...