Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
5,053
questions
0
votes
1answer
17 views
FOREIGN KEY Constraint Failed: SQlite3
I have two tables, created as below,
sqlite> CREATE TABLE Artists(
...> ArtistId INTEGER PRIMARY KEY,
...> ArtistName TEXT NOT NULL
...> );
sqlite> CREATE TABLE Albums(
...&...
0
votes
0answers
24 views
Should I use two indexes? [duplicate]
I would like to create some tables (including users table and products table) inside MySql Database with those index for each tables:
Private Index (uses int), for relational table purpose only
...
0
votes
1answer
45 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
...
2
votes
0answers
40 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
25 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
16 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 ...
0
votes
1answer
29 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 ...
0
votes
0answers
41 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 ...
-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 ...
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 ...
0
votes
2answers
51 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:...
0
votes
1answer
32 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
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
15 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!
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, ...
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
2answers
108 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 ...
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 ...
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
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 ...
1
vote
1answer
26 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
2answers
74 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 ...
3
votes
1answer
73 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
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
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 ...
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
35 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 ...
-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
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 ...
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
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
0answers
26 views
Vote based approval system in DBMS
I try to create a system to auto approve proposed posts by popular vote. I am currently evaluating what portions of this I can do within the DBMS and if it is sensible. I use PostgreSQL and I can go ...
-1
votes
2answers
37 views
Separate table or a enum on a client? [closed]
I have several tables and many of them has a "status", "measure_unit" and the like columns in them.
There're 2 approaches to work with columns such as "status".
Create separate tables "statuses", "...
-1
votes
1answer
47 views
What is the correct way to join this tables? [closed]
what is the correc way to join this tables?
Party, person and organization:
Tables
Party: id, party_type (person/organization)
Person: party_id, name, etc
Organization: party_id, name, etc
Other ...
1
vote
1answer
65 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 ...
0
votes
1answer
26 views
What would be best data model for finding difference within n, n-1 and n-2 elements?
This is from betting domain which has something that is called a long list: a list of a "home team win/draw/away team win" markets for 13 games.
A punter can select any combination of the possible ...
-2
votes
1answer
40 views
Differentiate data between test and prod data in sql server [closed]
we have a production database in ms sql server and my team is also testing on that same prod database. I want to know which are test data so in future I can delete these entries. How do I separate ...
0
votes
0answers
13 views
sqlite db design for pdu routing
I have no SQL experience whatsoever, and I'd like to map power distribution cabling for monitoring and management purposes using sqlite.
A managed PDU (power distribution unit) can turn on or off any ...
-1
votes
1answer
26 views
How to export specific columns from a SQL table
I am cleaning some data bases and was wondering that if there is any proper way to export any single column and all it's entries from a SQL table.
Being specific, I am dealing with SQL tables in ...
6
votes
0answers
36 views
Efficiently store large list structure in RocksDB so that the data can be retrieved in pages
Description:
RocksDB is a key-value storage so we can simply serialise the list of objects & store the value corresponding to a key. This would be ok if the data in the list is small enough.
...
0
votes
1answer
28 views
Best practice for other Table Data in an Order
At the moment I try to create a good Database structure for storing all Data belonging to an Order/Inquiry.
I use MySQL and Python at the moment and for now it isn't an real DB, just learning and ...
0
votes
1answer
32 views
One table for all persons
i have one table for all person (with fields ex. name, last name) because each person can has one or more role (customer, lead/prospect, supplier, employee).
And i have tables (customers, leads, ...
0
votes
0answers
21 views
implementing one to many relation between 2 dimensions in dataware house
I am designing a Dataware house for the Stackoverflow job section. I have two dimensions: job opportunity and company. every record of job opportunity is referenced to only one record of company ...
0
votes
0answers
42 views
Database design for metro train route stations and timing
I am making a city metro train app, and for that, I need some advice from you all experts on how to design a database for the following requirements.
The metro has multiple lines (see table), and ...
0
votes
2answers
25 views
Best way to enter data in DB that is spread out date wise
I have an Excel file that, to keep it simple here, has 3 columns: ActivityID, ActivityName and TotalHours.
TotalHours column is basically all the hours allotted to that specific ActivityID and the ...
0
votes
1answer
25 views
Reports with recurring events DB Schema in SQL Server
I'm trying to create a scheduling reporting feature where a user can schedule a report to be received "every 1st Monday of the month" or "every last day of the month" or "every week on Tuesday".
I ...
0
votes
1answer
48 views
String or separate Table
I'm creating the DB for a system and in it is an alert system, where, when a certain action happens, or when certain information is present, an alert (via email) is triggered. I'm allowing users to ...
0
votes
0answers
22 views
How to structure cancelled orders?
I'm working with NoSQL Database "Firebase", and I have a node that contains 6 categories "services" in everyone I put an array of orders well?
now when the user logged I get/fetch all orders bassed ...
