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

learn more… | top users | synonyms (4)

5
votes
1answer
153 views

Data inconsistency prohibition if a table refers to another via two many-to-many relationships

I have following database design (by => foreign key constraints are depicted): Company[id] CompanyRealm[id, company_id=>Company.id]many2many Project[id, company_id=>Company.id] ProjectRealm[id, ...
1
vote
3answers
62 views

Relational tables with same content, custom solution

So let`s say that I am building an app where I can add comments for FOO. Normally I would have something like this: Table foo and table foo_comments, where foo_comments contains id, foo_id and other ...
0
votes
0answers
31 views

Intergrated or seprated ? One row with multiple data or multiple rows with one data [duplicate]

I'd like to create a Friend system like facebook What type of DB design do you suggest ? 1. id | userid | friends_id ---------------------------------------------------- 1 ...
0
votes
2answers
41 views

Is one-to-one relationship required?

I have these three tables on my database: There is a one-to-one relationship between Code and Aggregation table (an AggregationId is always a CodeId. And of course, a CodeId is unique). Note: A Code....
1
vote
1answer
34 views

One to one relationship naming conventions

I'm designing an ER diagram with MySQL Workbench. I have two tables with a one-to-one relationship. Both tables have one column as primary key, but they don't have the same name. Take a look: But ...
0
votes
0answers
24 views

MySQL Design for bulk inserts

Consider the following table structures as below, these tables can go beyond millions records received_goods rg_id int(10) PK (auto_increment), name varchar(128), ... storage_details rg_id int(10)...
1
vote
1answer
50 views

Create a stored-procedure to insert multiple rows on a junction table and other tables

I am new to Databases and Design and needs some help on a INSERT issue. I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need ...
3
votes
0answers
50 views

ERD for a Contract between a Writer and a Publisher: Is it correct, can it be simpler?

I created an entity-relationship diagram (ERD) for a book publishing business context that I will detail bellow. Entity types The entity types of interest are: Writer Work (as a book) Publisher ...
1
vote
1answer
111 views

Setup MySQL Master to Master replication/automatic failover when each Master also has a Slave

Thanks in advance. I have always worked with SQL Server and never really had any exposure to MySQL. I now have to determine the preferred setup for MySQL when there are four servers involved. Ideally,...
2
votes
0answers
59 views

Database design - reusable elements (templates)

Suppose I have a table that stores items of a very specific type. Items of this type are stored in a bank for future use, and they can be changed specifically for a situation without being changed in ...
5
votes
2answers
76 views

Stable triangle relationship?

I have a table that represents a specific type of action. Let's call it [ACTION]. An action can be performed by a user ([USER]), so I have the relation [USER] 1:N [ACTION]. The user can have ...
0
votes
1answer
33 views

Access Web App query 3 Tables with aggregatios - Need a workaround

I'm designing an Access 2016 Web App for inventoring our computers. The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC. The computer-value in RAM ...
3
votes
0answers
49 views

How should I use DynamoDB table

I have a list of items. Each item is attached to several urls and each url has a score number. So I have something like this : How should I organise this in dynamoDB? How should I format my Json ...
0
votes
0answers
24 views

Modeling question - Meta and instance

In a relational DB I'd like to model this: every customer_type has one module_set (1:1) a module_set has several modules (1:n) every module is different, and the user can activate them & reorder ...
1
vote
1answer
47 views

Store table names to reference other tables

I'm designing a webapp where users can add widgets to their pages. Right now I have only two types of widgets that are different in nature, so they have different attributes, but both of them are used ...
2
votes
1answer
31 views

Table design for ticket sales

I'm designing a database for a ticket sales system in PostgreSQL, where the admin user can create a batch of tickets for a specific event. All tickets for a given event have the same attributes (like ...
0
votes
1answer
32 views

Should I divide my table into simpler ones or should I leave it the way it is?

I have a table which must store data related to an examination The structure of the table is as follows Table name: Exam_Routine | Year | Date |Stream | Paper_Code Since I have noticed that ...
1
vote
2answers
52 views

Primary keys, clustered indexes and partitioning

We store data for financial records, splitting it up over 4 tables. I'm at a bit of a loss as to what's optimal for our setup here, or at least which direction to head in as I'm getting a lot of ...
2
votes
0answers
19 views

Attribute: Majority single value, but multiple on occasion

Here is the situation: There is a dimension with a natural key that is a good candidate for a primary key. However, there are a few columns that can have (and does have - 10% of the time) multiple ...
0
votes
0answers
29 views

how to remove create relation foreign key in phpmyadmin

I am having problems in PhpMyAdmin Designer Section. I'm not able to do anything like I cant even move them to see their relations. When I try to click on tables the screen shown in picture appear ...
0
votes
1answer
164 views

Name of a descriptive text field? [closed]

I have seen descriptive text field named as DESCRIPTION, REMARK, and NOTE. They seem to mean the same thing. So what is more appropriate than the others?
1
vote
0answers
17 views

Conceptual data model for warehousing objects [duplicate]

I'm student and new to databases. I must conceptualize a database model for warehousing video games using a relational database management system (MySQL). Every objects in the warehouse have common ...
1
vote
1answer
38 views

Multitenant application: several tables or several IDs?

We are designing a common product to serve several companies. The most convenient way to do this, technically speaking, is to deliver a database to each customer. But we predict that, depending on ...
1
vote
0answers
22 views

How to design for scalable set operations?

This is the requirement for an educational application to teach words to language learners: Learner can declare that he/she knows a given word Learner can memorize the given word (to be managed by ...
10
votes
3answers
810 views

Blockchain (Bitcoin) as a database?

I was reading this BBC News article and the following excerpt, caught my attention. It sounds like Always On Availability Groups or High Availability Mirroring, maybe with security automatically ...
0
votes
1answer
26 views

database design help with multiple table relationships

Been scratching my head on how to set this up and looking for your help. Trying to create an API and not 100% how my tables should relate to one another. So lets say I have users, food, and food ...
1
vote
1answer
52 views

How can I better model the relationships in this project?

I'm developing an internal tool (as an intern). We deal with a lot of hardware, which is all custom and managers have a hard time knowing who is developing on what machine. The three main things to ...
1
vote
1answer
128 views

Slot Time Challenge - Doctor Appointment database schema

I am trying to make a doctor appointment system where patient online will have two options: First time visit (this visit should be 20 mins) Follow up visit (this visit should be 10 mins) ...
7
votes
2answers
105 views

Transposing hierachical data from one VARCHAR to two INTs

THE PROBLEM The table where the data is recorded is CREATE TABLE [dbo].[Almoxarifado]( [idAlmoxarifado] [varchar](20) NOT NULL, [tipoAlmoxarifadoId] [varchar](30) NOT NULL, [entidadeId] [...
1
vote
1answer
43 views

Data transferring

Is there any practices to transfer data from two databases that have different structure of table ? For example : old system : table [folder] with col: FolderId, name, age, sexe, date, orders New ...
0
votes
0answers
73 views

Which NoSQL Database is best suited?

I have been reading a lot of blogs trying to determine which NoSQL database would be best. I am looking at moving from file based (xml) storage to using a database. The application will be storing ...
0
votes
0answers
32 views

Mongodb database design

I'm planning to implement this schema in Mongodb, I have been doing some readings about schema design, and the notion was whenever you structure your data like a relational database you must be doing ...
5
votes
2answers
78 views

Postgres Update Limit

I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID. For example, say I have a users table and login_coordinates table. Every time the user logs in, ...
3
votes
2answers
63 views

When do you know that you've made a design flaw in a SQL/relational database?

I've been working with a a relational model for a work project over the last few months, and I've gotten to the point where I'm wondering if I've made a mistake in my model's design or implemented ...
5
votes
2answers
290 views

Table Relationship - One to Few (Many)

I know the title may seem confusing but I have yet to find a solid answer to this design pattern. Let's say I want to create an Employee table and have multiple addresses for each employee. So this ...
0
votes
0answers
29 views

Time Series on Postgresql

I am developing a software that have a dataset time series like this: id_equip, Timestamp, Double v1, ..., Double v30 The size estimate is 5.000.000 rows by year and the time of response is very ...
0
votes
1answer
62 views

Maximum number of TEXT/BLOB columns per table/performance of table with many of them?

I've created a system that generically imports CSVs into a database table with metadata columns for keeping track of when a CSV record has been changed, and it allows filtering on CSV columns instead ...
0
votes
1answer
41 views

Altering column data SQL [closed]

I have a set of car data, one of the columns represents the registration year. I want to change this column to age. As the data is from 2010 I assume that the following statement should calculate the ...
2
votes
1answer
83 views

Many to many between lots of tables?

TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended? Each body type has its own table Each car in each body type table can has several features ...
1
vote
1answer
40 views

Why does 3NF Normal form allow transitive dependency of prime attributes?

I'm just a beginner in Relational Design, When i came across the definitions of 3NF and BCNF. According to 3NF, Any non-trivial functional dependency X-> A shouldnt exist unless X is a super key or A ...
-1
votes
1answer
33 views

Multiple references to another table, but I don't know how many

I have two tables in a database, folowwing this model: scientists : id (int) | name (varchar) | surname (varchar) | publications (int) publications : id (int) | fulltext (varchar) I would like to ...
0
votes
1answer
38 views

How to create a table in access and automatically fills it with information from a query? [closed]

Is it possible if I create a table that will lookup for a value filtered from a query (consult), in this case is duplicated "Name"s from a Employees registring table and automaticly fills up that ...
0
votes
0answers
18 views

Product Features table for No Sql Databse

I want to design database to save products features. Actually i want to store all features of mobile given on this link http://www.gsmarena.com/samsung_galaxy_j7-7185.php After search on google i ...
0
votes
0answers
54 views

Normalized college database desgin

I am trying to create a college course database. The College offers UG and PG courses in various departments. Departments include program streams like MCA, MBA, and Engineering. MCA courses may be ...
0
votes
0answers
28 views

Modeling a database with UML scheme and every type of relations

Good evening! This is my second database designing, the first one can be found here. I want to find the relational scheme of bridal registry database given in the following French UML graph: I ...
1
vote
1answer
38 views

ON DELETE CASCADE doesn't work even storage table is InnoDb

I tear my hair with the ON DELETE CASCADE Clause. In fact I have two tables I'm on linux and I have installed lamp with phpmyadmin. That's the tables : CREATE TABLE a( Idatable bigint(20) ...
2
votes
0answers
48 views

Looking for suggestions to model a database of Electrical Components

We have a database of electrical components and I am trying to make sure I have a good design. I am not a database designer (my comfort zone is designing with these hardware components), so I am ...
7
votes
2answers
178 views

Normalizing a table with a field that generally uniquely identifies a row, but is sometimes null

forgive me if this has been asked and answered before. I'm roughing out a schema for an inventory management system, to be implemented in PostgreSQL. All of our products and services have a sku. ...
0
votes
0answers
19 views

SQLite text columns

When having a table in SQLite that has a TEXT data type, is there any implication if the length of the contained string differ significantly?
2
votes
2answers
80 views

How do I guarantee inventory numbers are unique across all tables?

I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords ...