Questions tagged [database-design]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
5 views

Doubt about Database design (newbie)

I am learning about Database design, I have doubt about some database design conepts I want to know which is better design between two design Design 1 there is two tables: Object and Field ...
0
votes
1answer
366 views

Parking Spots Availability Design

I have a reservation table that has from_date, to_date, and parking_location_id. What is the best way to calculate location spots availability for each location and each hour? I tried to write a ...
0
votes
1answer
91 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 strings primarily. Actually, users work on these strings, as normal mortals,...
2
votes
0answers
36 views

Composite foreign key design question

Suppose you have three tables, t1, t2, and t3 with the following relational fields. t1: id (PK) t2: for good business reasons, t2 has a composite primary key on {t2.id, t1.id} id t1.id (FK) t3: ...
-1
votes
0answers
19 views

Simple inventory management db design - polymorphic association?

I’m designing a web application for internal use to inventory-manage returnable/reusable widgets (a physical product). I anticipate around a 100 or so ‘stock/widget transactions’ per day. In simple ...
-3
votes
0answers
15 views

SQL querry for orders each month [closed]

The table contains the following atteributes: order_id (unique) customer_id (repeated) mobile_no (repeated) order_month (repeated) I wanted to find out for each customer_id the respective number of ...
14
votes
5answers
26k views

Pros/Cons of using multiple databases vs using a single database

I was working on a new project which has the requirement to use 7 databases, arguing that performance, stability, optimization are more easily implemented. While I don't agree, I'm having trouble ...
0
votes
1answer
27 views

BCNF decomposition step without functional dependencies

I am performing the BCNF decomposition for the relation R = (A, B, C, D, E), that has the set of functional dependencies AB -> C, CD -> A, C -> E, and C -> B. After checking that the ...
4
votes
2answers
1k views

Two ways to define may-have-a (1..0-1) relationship

Today I have discovered for myself that may-have-a, one-to-zero-or-one, (1-0..1) relationship can be implemented in two different ways: Let A be the "main" table (left side of ..) and B be the "...
3
votes
2answers
965 views

Table design for “Users & Groups” plus “Groups within Groups”

I need to represent users and groups, where groups can be nested within other groups. (Not my design choice – I am replicating another system and must mimic this behavior. I have no access to its ...
0
votes
0answers
40 views

E-A-V entity Attribute Value Generic Model with Min Max Values

I have an entity consisting of several attributes (can be added, can be removed from entity - and new attributes made as well). I need to be able to store the values - so for example I have a list of ...
1
vote
1answer
17 views

Its necessary to relate the ComplaintMessages entity with the User and Admin entities or is enough to relate with the Complaint entity? (ER Model)

I'm in doubt in the ER model of a scenario where a user can make complaints. The user enters the title and the description of the complaint and submits the info. A site admin can see all user ...
2
votes
0answers
36 views

How to handle / model “cost of provided spare parts per PO line item” where one spare qty is divided between multiple line items? / Postgres 11

Problem My objective is to help improve the data modeling / handling of my company’s present cost-of-goods [‘COGS’] calculation. Everything in the current Postgres 11 table structure is pretty ...
0
votes
2answers
3k views

User roles and permissions

Look at the attached graphic. I'm trying to keep this as simple as possible. Basically I want to have multiple admins(or superusers) who own regular users who own sites. Admins can give regular ...
0
votes
2answers
49 views

How to store daily records in SQL Server 2017 database? [closed]

So, I am working on Angular + .NET Core Web Appliction with SQL Server 2017 as the database. So, the new feature requires that we have to store daily data of users belonging to all countries. Example:...
1
vote
2answers
38 views

database design for car part site. (relational database)

I am working on a directory website for the car's part, in this site the shop owner can register themself and then the user can search for the part based on the year, model submodel and type of the ...
3
votes
1answer
72 views

How to store chess positions in a relational database (in particular, Postgres)

I'm exploring the idea of building a website that involves analysis of chess positions. Chess positions themselves are described using a format called FEN, e.g. the starting position for a chess board ...
0
votes
1answer
390 views

Django Multi Table Inheritance and Preserving Child and Child History

Place can have many different one to one fields, that’s why it can have bookstore, restaurant, and hardware store. But can the same place be both a bookstore and a restaurant at the same time? Now if ...
-1
votes
0answers
26 views

Table structure for rooms availability

I am building a website to manage room availability within my organisation. It will be a mixture of devices reporting whether a room is available or not (Occupancy sensors) but also users will be able ...
0
votes
2answers
632 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 ...
2
votes
1answer
178 views

Lossless Join Decomposition Criteria

On Wikipedia, it says: The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in F+ (where F+ stands for the closure for every ...
-1
votes
0answers
33 views

How to create multi-level category table structure in MS SQL [closed]

The scenario is multi-levels of category connected in a tree format. Let's say I have CategoryLevel1, CategoryLevel2, CategoryLevel3... and so on up to 10 levels. Each level is mapped with the parent ...
0
votes
1answer
31 views

Mixing sharding and Replica in MongoDB cluster

We know there are two ways to create a mongoDB cluster :- 1.) Sharding 2.) Replication My question is can we combine both sharding & replication like we have in elastic cluster?
1
vote
2answers
73 views

Possibly inconsistent database?

Suppose I have a database schema with 3 tables - Area, Work and Author. Area is a table which contains the list of Areas of interest of an Author / Areas in which a Work has been published, for ...
1
vote
1answer
28 views

Mongo Cluster sharded replica cluster design question

I have 5 old servers, each with single socket CPU, 4 physical cores(8 logical) and 128GB of RAM. I am getting started with mongoDB and plan to deploy MongoDB 4.2.2 on my 5 servers for a lab ...
0
votes
1answer
261 views

How to insert records in two different tables with PDO? [closed]

I have questions concerning signup process using PDO. I need to insert different records in two different tables that are connected. Employee id | f_name | l_name | position | paygrade | users_id |....
1
vote
2answers
107 views

Database design issue, with multiple variables and constraints

I'm using PostgreSQL. I have the following entities Categories, Items, Attributes. Relationships: M2M between categories and items (an item can be in multiple categories, a category can have ...
1
vote
1answer
44 views

The best practice for SQL + NoSQL database design [closed]

I’m in process of designing database architecture for my diploma project and I have second thoughts about different solutions. I know and understand all my objectives can be done within relational ...
0
votes
0answers
14 views

Is there a way for the temporal tables to log to history table only when any of the value changes? [duplicate]

I want to Audit/insert the record to history table only when any of the value is modified in temporal table. Thanks!
0
votes
1answer
455 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 ...
0
votes
1answer
34 views

Can I modify a column's data type against a live table in Postgres?

I have a sparse matrix table where every attribute is citext. There are some indexes built against a few of the fields. I want to change these columns back to varchar. Can I do this against a live ...
1
vote
0answers
17 views

An efficient database design for recording complex contact/configurator form

I know it's a quite generic question and probably opinionated. I don't know where to ask. Maybe you could point me to the right direction. Currently I have a very big 'configurator', 10 steps long, ...
1
vote
1answer
25 views

When is running a graph database like OrientDB in cluster mode required?

OrientDB can run in a distributed environment, supports ACID transactions and peer to peer replicaiton. According to the book "NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot ...
1
vote
1answer
20 views

DB Design - Assigning users to multiple sites with different roles

I am working on a DB design that lets me manage roles and permissions for each user per site. These are my business rules: A user can manage multiple sites A user can only have one role per site A ...
1
vote
2answers
1k views

Optimize large table design

First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a ...
0
votes
0answers
26 views

Third Normal Form and Boyce Code normal form

I know that this is not a question answer site but for sake of explaining my doubt I have to post the entire question.. Consider the following statements. If relation R is in 3NF and every key is ...
5
votes
3answers
115 views

Optimizing table/indices for getting the latest row (filtering by one additional column)

I have a SQL Server table (SQL Server 2012 SP3 Standard edition) that stores a bunch of configuration information (basically text blobs) for different organizations. The schema is something like this: ...
0
votes
2answers
98 views

Should I represent two distinct relationships via only one associative table?

I have a database to store the news of my website. The news entity type is associated with two different ones; images (which from the point of view of the app users are grouped in a gallery) and ...
0
votes
1answer
33 views

Database design - logging of same value when recorded in multiple tables

I need to record changes to a particular value, which is read as part of multiple events that are all separately tracked, and be able to view a log of all of the values later. I think an example will ...
-1
votes
3answers
52 views

ID, UUID and “deleted_at” columns, How to avoid performance degradation?

Consider this table, but with thousands of records, on PostgreSQL: | id | uuid | color | ... | deleted_at | |----|---------|----------|-----|---------------------| | 1 | 4fc1... | red ...
3
votes
3answers
7k views

Grouping data into 5 minute intervals within a time range

I am trying to prepare data for a candle stick chart. I have some records on MySQL database as shown below: | Rate | Date | |----------|---------------------| | 0.001111 | 2017-11-...
1
vote
1answer
23 views

Query NOT Workinng in OPEN OFFICE BASE

select * from "StudentMaster" where "PhoneNo" = NULL; Syntax error! i want to see all record which don't Have Phone number ! I have try this all thing. select * from "StudentMaster" where "...
1
vote
1answer
134 views

What would be an appropriate schema for this real life scenario?

A Company C provides an enterprise product to organisations (O1, O2, O3... On) and their sub-organisations (SO1 ... SOn). The product offers the organisations a database of content which is created by ...
1
vote
0answers
28 views

Schema design for an entity with 'heterogeneous' status types

Consider a notification system which gets some event data and list of business email addresses as input (the list is basically computed by looking at which users/addresses are subscribed to which type ...
-1
votes
0answers
14 views

MS Access textbox on a form is rounding numbers [migrated]

On a MS Access form, I have a text box that is being auto-filled with data from the second column of a combobox. The combobox is being populated with the results of a query. The query results are ...
0
votes
1answer
34 views

What are the downsides of using varbinary[max]?

I want to set up a table to store waveform data from an oscilloscope. The table would have a column of varbinary[max] because the waveform data from the scope is returned as a binary sequence that ...
0
votes
4answers
84 views

Trouble deciding what/how many tables to use; normalisation suggests one

More of a conceptual question for the community, as I am not "thinking with databases" as much as I would like to just yet. I am using Oracle Database 18c Express Edition with SQL Developer. I have ...
-1
votes
1answer
29 views

select data from database using a logical field

I have the following table in my database: +-------------------+ | rooms | +-------------------+ | id | | title | | single_bed_count | | king_bed_count | +--...
1
vote
1answer
63 views

Database schema initially not designed for scaling, how to optimize it?

I developped 2 years ago a back office for helping a logistic company to manage the deliveries. At this time, the company only had one "partner" (let's call it Partner A). The main table is ...
1
vote
1answer
85 views

Database design for articles with localized prices

I'm designing a database for a shop for having some fun using Postgresql (but the database should not be really important here, right?). I'd like to design for each article in the catalog multiple ...