Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

The development of the conceptual schema and/or the logical model and/or the physical settings of a database.

1
vote
1answer
71 views

How to make a table that could add many same number of different item?

I wanted to create a table to store coupons of users. For example, there is 3 coupon which is A, B and C etc. A user can have 10 coupon A, 20 coupon B and 5 coupon C etc. How should I create a table ...
-4
votes
1answer
35 views

Each record in a table references another unique table [on hold]

I have a table every record in which references another unique table with unique set of fields. How correct is this approach? Is there a better way to organize data in such scenarios? Database schema:...
-1
votes
1answer
31 views

Database design for a timesheet app?

I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user: To enter for each day of the week the amount of time he ...
1
vote
1answer
26 views

Is it good practice to have relationship between the tenant table and all other tables in a multi tenant application?

I am developing a multi tenant SaaS that uses Azure SQL for data storage. I make use of sp_set_session_context to make sure that any query only touches records relevant to the current tenant. I ...
1
vote
2answers
31 views

What is the correct way to enforce unique “IS-A” relationship?

Let's say we have a Person relation, and with that Employee which IS-A Person, Manager, which ISA Person as well. What is the correct way to ensure that a Manager isn't an Employee and vice versa ? ...
1
vote
1answer
15 views

How to reconstruct functional dependencies from 3NF decomposition?

I have the following decomposition for the relation R(A,B,C,D): R1 = (A,B), R2 = (B,C), R3 = (A,D). It is given that AD is the only candidate key for the original relation and that R is not in BCNF. ...
0
votes
0answers
13 views

How should I create product variations & can my current schema be improved? [on hold]

I am creating my own CMS but I have some problems with my database logic. There are a 2 requirements I kept in mind: It has to support multi-langual features. Product variations should be treated as '...
2
votes
0answers
28 views

Surrogate foreign keys allow inconsistent data - which normal form does this violate?

We have inherited a system which has surrogate keys on every table (that is, an auto-increment primary key, id). All of the foreign key constraints are on individual surrogate keys, and this seems to ...
1
vote
1answer
42 views

How to model a car (year, make, model, trim)?

I'm trying to design a database to represent cars (specifically: year, make, model, trim) for a car dealership application. Based on my understanding of the data, I've come up with the two database ...
0
votes
0answers
30 views

Calculating monthly data as it is inserted [on hold]

I'm very new to databases and database design, so apologies if this is a silly question or has already been asked - I was unable to find any answers to this via Google, though. I am currently ...
0
votes
0answers
19 views

How to design database for storage of Items in Inventory stores? [on hold]

This is basically for a Production Management System that I'm currently working on. I have more than one type of Items to store in inventory (Products/Parts/Materials). I want to store them in ...
0
votes
0answers
15 views

Designing a globally distributed database

We are trying to design a globally distributed Azure Cosmos DB and we’re not sure how to best do that: There is data that should be replicated in every region to improve read performance. This data ...
0
votes
0answers
23 views

MYSQL - Best way to search for a name or category like in the Yellow Pages

I have a website that is a business directory like YellowPages.com . I have the following table containing all the companies: mysql> describe companies; +-------------+--------------+------+-----+...
1
vote
0answers
21 views

How to check if functional dependencies are preserved?

I have the following functional dependencies for the schema R=(a,b,c,d,e,g): {b→c, dg→ce, bc→dg, e→a, g→bd}. I want to decompose R into two schema's: R1=(a,b,c,e) and R2=(b,d,g) and make sure the ...
0
votes
0answers
19 views

A flexible and robust schema for storing precomputed values/metrics

Background I'm dealing with a table that contains a lot of historical data. What I am doing is generating a bunch of metrics based on different combinations of column values and date periods (e.g. ...
1
vote
1answer
21 views

How to open a file in MySQL event scheduler?

I am currently creating my first event in MySQL: CREATE EVENT check_for_uploads ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ON COMPLETION PRESERVE DO // I would like to open a .php file ...
-1
votes
0answers
23 views

why a foreign key must reference either the primary key or a unique key of the parent table?

in my understanding, reference is to avoid some value does not exist in the referenced column. So WHY the referenced column must has unique constraint? The DBMS can just check each row if that value ...
1
vote
1answer
30 views

How to solve multiple minimal covers for functional dependencies?

I have the schema: a,b,c,d,e,g and the following functional dependencies: b->c, dg->ce, bc->dg, e->a, g->bd for which I want to find the minimal cover. It seems that more than one ...
0
votes
1answer
21 views

How to find minimal cover when attributes have equal chance to be not necessary?

I have the schema which consists of 6 attributes: t,s,r,w,e,g. For the schema I have the following functional dependencies: s,w --> r;t,w,g --> r,e;s,r --> t,g; e -->w; g --> t,s;r,w,...
1
vote
0answers
17 views

How to model a database for prices that vary depending on their categories and parameters?

My first post on DBA! Advanced apologies if I made any mistake. Before jumping into the schema and tables, I would like to share what I am trying to achieve first. I am working on sort of a courier ...
-1
votes
0answers
40 views

Multiple Database design doubts [closed]

I'm in a project where a partner is in charge of the development of the future database structure in MariaDB, the goal is to store all the information generated in an ERP(Web-PHP Based) over 13 ...
-1
votes
0answers
28 views

Insert update history of sql child table

I'm recording all insert and update on TaskDetail table using a trigger,Now I want to assign multiple staff to a task, But if staff id stored in different child table how can I track audit history, I ...
0
votes
1answer
24 views

How can I manipulate database in stack n-up order? [closed]

I need to print a mail merge 3-up. The database ( Micorsoft Access ) is in order of ID 1 to 1845. Instead of printing sheet#1 (ID 1, 2, 3) I'd like to print sheet#1 (ID 1, 616, 1231) and so on. ...
-2
votes
1answer
36 views

Issue with a products database

I have a problem concerning the correct design of a relationship. My database has only one table called products, which has the following columns: id, name, categories, price, description, extra data ...
1
vote
1answer
31 views

Storing files with extension in database as BLOB

Recently during development process I've faced file storage issue. I'm not sure which solution is the best one that's why I decided to write this question. I don't have a lot experience of database ...
1
vote
2answers
36 views

Normalizing the Test Table

I'm a web developer looking to leverage more of the power of my RDBMS. I'm currently learning about normalization of data and database design. In the book that I am reading, we started off with one ...
-2
votes
0answers
9 views

Set List attribute in JPA when null? [closed]

I am using EclipseLink as my JPA implementation and Postgresql as my DB. JPA: @Struct(name="label,.....") @Entity @Table(name="\"XYZ\"", schema="\"XYZ\"") @NamedQuery(name="...
1
vote
1answer
50 views

Modelling a database for products, categories and features

Good day everyone. I am building a database for an application and I have thought of two different approaches for achieving the same result, and I would like your opinion. Is basically the classical ...
-5
votes
0answers
17 views

Composite Primary key null in 1nf is it possible [duplicate]

[![** To make the collection table in 1nf object id and Provetype is required to be the composite primary key but as i know that part of primary key cannot be null now i have a confusion in the ...
1
vote
0answers
23 views

News feed database design in mysql [closed]

**I have added SQL Fiddle : http://sqlfiddle.com/#!9/b389636/6 ** I hope it will help a lot of people. Thanks for your time. :) I am trying to design a news feed system that has feed and friends ...
0
votes
0answers
35 views

How to abstract duplicate data to separate table? A stab at normalization

New to PostgreSQL. I have a table like so called "books." Modify id isbn title author year edit 2 380795272 Krondor: The Betrayal Feist 1998 edit 3 ...
1
vote
1answer
41 views

Many to Many Table with multiple same-column references

I'm designing a document translation DB and struggling to connect the non-generic many-to-many table. Each document has a title and summary. Translated Document table: CREATE table ...
1
vote
1answer
33 views

Clarification on Cardinality and Participation

I'm currently redesigning a schema with 20 or so tables and my first task is to create an ERD from the existing tables/constraints/relationships. This has proven more difficult than I expected because ...
0
votes
0answers
24 views

MongoDB Design - related tags

Given a tag, I want to know which are the most frequently used tags associated with that tag. The database has to be filled with an arrays of tags. The arrays of tags have average 20 elements. The ...
-1
votes
3answers
39 views

Is there any way to keep rows with same id sequential even if they are not stored sequentially?

Edit: I think i used the wrong word here. By "sequential" i don't mean that everything aid 1 enters has to be in the order of the time at which they enter it. What i mean is that they should be ...
8
votes
3answers
3k views

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY? Is there a possibility that two or more different INSERTs, get the same CURRENT_TIMESTAMP?
1
vote
2answers
28 views

Question about database structure for network visualuzation

Hello folks. I have a matrix I created in Excel that shows 17 nodes, each node's connection to every other node (if any), and that connections strength. Note the error in the image, cells that ...
1
vote
0answers
26 views

How to create entity-relationship diagram for tables that can potentially be united by an “IS A” relationship?

I have this schema: create table hall( building-id int, hall-id int, capacity int, primary key(building-id, hall-id) ); create table semester( sem-id char(5), start-date date, eend-...
-1
votes
0answers
21 views

Database schema for a product management application

I want to build an api for managing products. I'm currently working on designing the mysql database schema. My end goal will be to use this to manage ski and snowboard rental equipment but before I ...
2
votes
0answers
96 views

Designing a database which can track stock movements

I work for a manufacturing company which sells machines to customers which have a control unit installed in them. We need to keep track of the locations of all the Machines after they are sold to the ...
-1
votes
0answers
27 views

Database design for complex roll-call system

I'm developing a roll-call system for the company I work for. I am in the 4th iteration of the software, trying different things to get it to do everything I want it to do. I want to involve some ...
1
vote
0answers
22 views

Playlists design for a music streaming app

So we have the following tables for a music streaming app, Table tracks ---- id name album artist Table album ---- id name Table artist ---- id name Now, each user is given a feature to create ...
1
vote
1answer
54 views

Storing huge amount of user game data

So I have this side project of mine which relies on me agreggation User game data from an API. What I want to store is the player encouters with other players and saving the result of those ...
0
votes
0answers
12 views

Design a show with competitions of different types

I am currently working on designing a schema for a scheduling system involving a show and different types of competition. The requirements are: A show can contain n competitions of different types (...
1
vote
0answers
19 views

Local Transport Fare design: how to relate departures, destinations and locations

I am doing a conceptual model for local transportation fare app The goal is for users to quickly check the current fare and the previous fare from one location to the other. Sometimes, the ...
-3
votes
1answer
32 views

What better design to support dynamic relationships?

Instead of creating a seperated join table for one many-to-many relationship, I will design a centralized table, called 'Collection'. It will contain whole relationship data for all tables in database....
2
votes
1answer
102 views

Is sql_variant compatible with First Normal Form (1NF)?

1NF rule says that we should not keep data of different types in one single column. Does it mean that sql_variant is not compatible with first normal form hence should not be used?
1
vote
1answer
28 views

Is there a standard database layout for repetitive information?

I was recently tasked with creating a database to store a modest amount of data. I am learning as I go, but my main hold is figuring out the best way to layout the data. The problem is that for each ...
0
votes
1answer
46 views

Skip ERD in database implementation [closed]

AS we know that entity relation diagram(ERD) is an important concept in database implementation, But I was wondering that can we skip this step when creating a small database containing a few tables i....
2
votes
2answers
139 views

Why is this query with WHERE, ORDER BY and LIMIT so slow?

Given this table posts_lists: Table "public.posts_lists" Column | Type | Collation | Nullable | Default ------------+------------------------+-----------+-...