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

0
votes
0answers
2 views

Creation of DB in mySQL to sort and filter data

New to this forum, so thanks for taking the time to read my post! ;-) Struggling to design the correct database / structure for what I'm trying to do. Let me try and explain the situation. We are ...
3
votes
2answers
32 views

Entity relationship problem

I think my question is stupid but I just can't figure out how to solve it: I have 4 tables related like this (it's an example): Company: ID Name CNPJ Department: ID Name Code ID_Company ...
0
votes
0answers
23 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
17 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
0answers
10 views

Modeling the living place of a Person [migrated]

I have a dwelling class which is a generalization of house and building. Those classes have their own properties, and the class floor is a composite of building. I would like to associate a person ...
0
votes
3answers
34 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. ...
1
vote
0answers
12 views

fast execution analitycs database with thousand rows to displaying in php [migrated]

i've table with a thousand rows and i want to creating analitycs with chart display my front end php my table structure is and how i display this data : by user_agent column i display operating ...
0
votes
1answer
23 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 ...
7
votes
2answers
63 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
48 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
25 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 ...
-2
votes
0answers
29 views

Entity-Relationship Modelling [on hold]

WWLS staff consists of lawyers and secretaries. Lawyers work on cases, while secretaries provide assistance to lawyers , while some secretaries assist only one lawyer, some of them assist multiple ...
3
votes
2answers
50 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 ...
-3
votes
0answers
20 views

duplicates of part.num to be summed and add another column called user [on hold]

There are 3 columns part num, Qty and MO. Each MO has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty. 1.There are duplicates of ...
1
vote
1answer
18 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. ...
6
votes
3answers
155 views
+150

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 ...
1
vote
0answers
54 views

Schema question

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 ...
8
votes
3answers
125 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
50 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
29 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. ...
0
votes
0answers
19 views

Best practice to create a database schema for Open Auth (Social logins)

I've done a database schema for user management alongs with their roles and permissions. The problem actually began, when I decide to integrate a multiple "Open Auths" such as Facebook Login, Google ...
2
votes
0answers
46 views

Networking (classmate like) website database model

I am working on a project that consists in building a networking website like the Classmate web site. I have proposed a database model based on the social media database model presented on the ...
4
votes
1answer
70 views

Better database design

I'm using a MySQL database to store data from my java application. In java I have two classes: The VocabularyList class has a name and holds a list of vocabularies. A Vocabulary has a vocabulary ...
2
votes
1answer
36 views

Database schema for passing requests to role based users

I am working on a Database schema where the scenario should be: There are role based Users; Role 1, Role 2, Role 3 and Role 4 There is a Request model (or class, or table) User with role Role 1 will ...
0
votes
0answers
40 views

mysql - suggestions for table design with huge number of columns

We have a table that consists of a huge number of columns (usually between 500 to 1000, depends on our clients websites). We are suffering from speed issues due to this, and we're looking for the best ...
3
votes
0answers
69 views

One table or four?

I have four entities: Absence { ID, StartDate, EndDate, Days, Hours, Reason } Overtime { ID, Reason, Date (Integer), Hours (Integer) } Time in lieu { ID, Reason, Date, Hours } Holiday { ID, ...
1
vote
0answers
24 views

Search a value from multiple column and multiple table [closed]

I have to search a value from multiple column and multiple table and each table is linked by foreign key and private key (in parent table , there is only one parent table and rest is child table of ...
5
votes
2answers
101 views

Which to store: miles or kilometers?

My app is launching to a US audience first, but I want to internationalize the database schema for future expansion. Obviously I will convert between the two units as necessary regardless, but are ...
0
votes
0answers
31 views

How to make a query from a database with schemas to a database without schemas, and vice versa, with DBLINK in postgres

I have a DB (DB1) in postgres 9.4 who use DBLINK to query another DB (DB2), and vice versa, using views and functions. But now i have to add 2 schemes to DB1 (CLIENT1 And CLIENT2) and i want when i ...
3
votes
1answer
60 views

How are databases able to process queries in parallel while still retaining correct order?

How do databases process queries in parallel (more than one query at a time) while still keeping the correct order (queries are performed in the same order that they are submitted)? Is there a kind of ...
0
votes
0answers
19 views

FastReport / Report Query Help - How do i convert a integer value into a string?

I am using FastReport to create my reports and I have an issue. I am trying to make a form with the data in ProductMaster.ITEMBUYINGPRICE. The output of this column is a 6-digit value showing the ...
1
vote
0answers
34 views

User Privacy Settings DB Schema Design

This is how settings for users are designed, to so they show on some other Stackoverflow pages. User =================== id e-mail password ... =================== User_Settings ================= ...
4
votes
1answer
181 views

How to model complex systems like networks in Postgres

I need help with modelling of a network involving switches (24 port, 48 port, POE, and a combination of those) and edge devices (computers, IP cameras, telephones). From an object oriented ...
1
vote
0answers
39 views

Which database technologies to use? [closed]

We are a small construction company that currently has a very old, outdated database system that has been written over the past 20 years in Visual FoxPro. I have now been designated with the task of ...
2
votes
1answer
10 views

Alternate in access database

I want to check whether a row exists or not before inserting a new one in Access 2007. I have the following working query in SQL Server but I'm having trouble converting it to Access. update category ...
0
votes
1answer
24 views

Are cut and paste of records from a table to another a correct pratice?

I am working on an app that does task dispatching. A user inputs a task with a start date and end date. The app gives the user a list of workers available and chooses to offer the task to some of ...
2
votes
0answers
34 views

How to implement search for world-wide Wikipedia/OSM places with alternative-names

I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages. Thus, if you search for ...
1
vote
1answer
42 views

Database design for a car dealers website

I am creating a new website for all the car dealers in my region to show their cars on and i need your opinion on the database I have created because I have some doubts. For instance, is it best to ...
0
votes
0answers
38 views

Does MySQL InnoDB compression combined with Windows compression save more space?

We have a very large MySQL InnoDB database, compressed using the following syntax at the end of the table declaration: ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 We also have windows ...
4
votes
1answer
38 views

Do I relate entities that are indirectly related when creating a student-course ERD?

I'm trying to create an ERD for a tutor center, this is what I understood from their operations: There is the tutor center director, then under her there are sections/departments. It, math/calculus, ...
0
votes
1answer
71 views

MySQL Circular Replication

I have this scenario where I have 4 MySQL servers (A,B,C,D), as this: Server (A) is always online, and serves as the main website. Servers (B,C and D) are online on random periods of the day, and ...
1
vote
0answers
24 views

Storing resource information efficiently

I'm looking for any advice on how to efficiently store some resourcing data for our app. For each project in our system we need to store the resources required and this comes in the following form: ...
3
votes
1answer
24 views

Inventory management in Rails

I'm creating a inventory management app in rails. I have an User Table and a Recipe table, there is also some tables with types of material (pens, sharpies, paper, and so on). Because there is a lot ...
2
votes
0answers
21 views

Is it a good way to use an auto increment integer field as the primary key? [duplicate]

In database design, is it a good way to use an auto-increment integer field as the primary key? For Example, I have a tenant table, tenant_id is unique. In this case, do I need a auto_increment key? ...
3
votes
3answers
87 views

What architecture is best to store a large 3 dimensional array in MySQL?

I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy: { x, y, ...
5
votes
2answers
138 views

Storing a Billing Address Best Practice in Orders Table

Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table. What I'm looking for is how I can set up my ...
1
vote
2answers
30 views

How to set-up a distributed data storage system for The Real Junkfood Project

Context The Real Junkfood Project consists of a network of pay as you feel (PAYF) cafes. We need to log the data from each of ~100 cafes, generating perhaps 50 rows each par day. I estimate that this ...
3
votes
0answers
100 views

Which is more efficient b+ tree or search (binary or linear)?

SELECT A FROM T WHERE M > 1000 AND M < 5000; I am unable to judge which of the below is best suited for the above query: Linear search Binary search on M B+-tree with index on M B+-tree with ...
2
votes
4answers
80 views

Normalization for postal addresses in US (address, county, city, state, zipcode)?

I've been trying to understand for the last few years which way is good for storing addresses. I've been getting "normalize all the way" but also "denormalize as much as you can" and I just cannot get ...
2
votes
0answers
31 views

Design Question - Stock and OnSale Table

Background : I'm designing a solution for a repair toy association. Since they have office - I'll use Access. Plot : Stock will store items - I choose this primary key (Item_Id,Record_date) ...