Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
4,707 questions
0
votes
0answers
3 views
How to design a database with product-dependent columns?
I am very new to database design. I have a task to create an inventory program with asp.net core and entity framework core.
The inventory program will be used to manage various kind of products. ...
-4
votes
0answers
20 views
How to get temporary table based on two queries [on hold]
select mst064_constunit,
mst007_unitname,
mst064_constcode as constcd,
mst064_constname ...
0
votes
3answers
46 views
table with no primary key
I have two tables
doctor (doc_id, Doc_name, Doc_phone)
Specialization (doc_id, specialization)
a doctor can have zero or many specializations, but I'm not sure what the primary key for ...
1
vote
1answer
20 views
Newbie question: database for a language school
I'm building a database to create lessons for an online language school. I'm not an expert but this is what I came up with. Each lesson will be composed by "blocks" that can be pretty much anything (...
0
votes
0answers
14 views
Normalization of the database [on hold]
How to express a foreign key in the normalization of the database which is dependent on two non-prime attribute in the same entity of the primary key?
2
votes
0answers
24 views
How to model a set of unique objects?
I have an entity type called 'Object', and I need to model an object set as a list of unique objects with variable length. Then, each object could belongs to many object sets.
Current entity-...
0
votes
1answer
16 views
Relation with many roles - Logical Model
I have an assignment where I am asked to draw a logical model. The data is for a hospital where each doctor can have three roles:
supervises a patient
requests for a procedure
carries on a ...
1
vote
0answers
26 views
Design for tables with the same column value but different names
I have the following three tables:
doctors (Doc_id, Doc_name)
patient (patient_id, patient_name)
admission (patient_id, sup_Doc, date)
What is the correct way to go about linking the doctor and ...
-1
votes
0answers
16 views
One long table vs many smaller
Our plugin-style platform has several plugins ( like forum, group, simple mail/chat, etc.) which all have share common features : file and event_log.
Their structure are rather simple and can serve ...
0
votes
1answer
29 views
Should you create separate tables for fixed options for a column?
As shown below, in multiple places in my DB Design I am creating tables for "options" if you will. Example, RFP Stages. Which would hold things like "Complete", "Bidding", etc.
I'm doing this ...
0
votes
0answers
19 views
Is it possible to change the color of relationship lines in MySQL Workbench diagrams?
I have MySQL Workbench Community Edition 8.0.15 installed on my Mac OS 10.14.3. I'm currently working on making an ERD with Workbench's EER Diagram.
My problem is that whenever I export my diagram, ...
0
votes
1answer
17 views
Using views with no insert table VS Audit tables
I(not a DBA) am considering designing Audit tables for an application that needs to be really fast as well fully audited (undo is not necessary). I referred to some from so and I found variations of ...
0
votes
0answers
15 views
Adding a surrogate key when modelling (conceptual) entity types [on hold]
I have two (conceptual) entity types: DOCTOR and SPECIALISATION, I believe the cardinality ratio of their relationship is many to many (M:N).
I need to break the M:N relationship, but I am not sure ...
0
votes
1answer
22 views
Where to place derived attributes
I have an assignment which requires me to design a database.
I have 3 entity types WARD, BED, and BED_ASSIGMENT (which is the "composite" entity type between BED and WARD).
The BED primary key ...
0
votes
0answers
17 views
Design Database with weekly changes [on hold]
I need to design a database with a defined column structure. This database has weekly updates and I need to keep a record of all the previous data to make calculations after a period of time.
What ...
0
votes
0answers
10 views
Accessing table from a different database [on hold]
I just started this job. And I have this senario where I can access the table database1.dbo.table1 with query as select * from database2.dbo.table1. There is no table1 in database2. But the query ...
0
votes
0answers
28 views
Designing a college major model
I'm developing a tool for college students to track their progress toward fulfilling a major and am seeking advice as to how best account for A or B requirements as well course groupings X-Y. For ...
1
vote
1answer
43 views
Best method of creating constant value [duplicate]
I have a query with a hard coded value which is also hard coded into my application (which has a comment saying that the value should be the same as the value hard coded into the query).
What would ...
1
vote
1answer
32 views
Unique sets of IDs from table
I want to represent mixture of arbitrary numbers of chemicals. Since the relationship between chemicals and mixtures here is many-to-many, I thought I'd implement it like this (simplified):
CREATE ...
1
vote
2answers
35 views
How to represent the relationship between artists and albums in an entity-relationship diagram
I'm learning and practicing with the E-R model trying to design a music industry database.
With this section of the diagram I have a doubt:
I suppose an album can be had by an artist and a band, not ...
-4
votes
0answers
38 views
0
votes
2answers
111 views
+50
Database Design for Cars
I'm designing a database to capture car data. Here's a simplified design for capturing year, make, model, and trim:
This should handle data like this:
2017 Ford Mustang GT
However, it turns out ...
1
vote
3answers
64 views
Database solution for a large matrix/very wide table
Suppose I have these two data frames (simplified for my question):
users
+---------+
| user_id |
+---------+
| 1 |
| 2 |
| ... |
+---------+
articles
+------------+------------+
| ...
0
votes
0answers
30 views
Parent/child table with include/exclude items
I have a table with parent-child relations. The relations can go n-level deep.
There is also a table with elements that belong to a group.
CREATE TABLE group_children(
id serial PRIMARY KEY,
...
0
votes
0answers
17 views
does a fact table always need to have a field for aggregate?
When I was studying data warehouse modeling, the first thing I came into contact with was the order fact table, and the product, user, date dimension tables.
The order table will have quantity, ...
0
votes
0answers
17 views
Looking for optimal DB design - game records
I'm designing a DB for an app, where people could save their game records and get some statistics. Game could be played 1x1 or 2x2 players.
I expect ~20 active users per day, if that matters.
I ...
0
votes
1answer
21 views
How to store data in look-up tables when some of it may be semantically different, but logically the same?
I am working on a system that receives communications from various laboratories. Each laboratory has two separate, independently operated data services which means that Laboratory A and Laboratory B ...
-1
votes
0answers
23 views
Script to fix MySQL columns to work with strict-mode [closed]
I'm trying to build a script to generate ALTER TABLE queries so that across whole schemas, columns are adjusted to be compatible with MySQL strict-mode..
I want it to be a simple as possible, making ...
-3
votes
0answers
45 views
Codd's rules demonstration [closed]
I am new to databases and I'm trying to understand Codd's rules. I have searched the web but I didn't manage to find any source to see a demonstration of Codd's rules. I am working on the database ...
0
votes
1answer
30 views
Combine many table into one table in mysql table design
I'm grappling to design table relation. Version is Mysql 5.6.10.
My case is like below
bill table, payment table, contract table, recruitment tables... are existing.
Currently we're considering new ...
0
votes
0answers
11 views
Conventions/Rules for the entity relationship modelling (ERM)
In an exam past paper I am asked:
Use Entity Relationship modelling to identify relations, including attributes and indicating primary and foreign keys.
The narrative is as follows:
A Pizza joint ...
-3
votes
0answers
14 views
A Comparison between Storage Key and Integer Position! [closed]
It is understood that
a) column store can be emulated in a row store by converting each column into one physical table and introducing integer position in each table.
b) In vertical store: ...
0
votes
2answers
29 views
Two composite foreign keys have identical columns which must be equal value
Let's say I have a relation: CourseRegistrations with composite primary key: (course_id, sem_id, student_id)
I have another relation: CourseEvaluations with composite primary key: (course_id, sem_id, ...
0
votes
0answers
23 views
Modelling and normalising a tourism activities database [closed]
Business rules
Screens and external schema samples
Normalisation attempts
Considerations and questions
How can I find which attributes would be needed to identify the date an activitiy was '...
0
votes
1answer
40 views
Avoiding a many-to-many relationship cycle between 3 tables
I'm designing a database for a IDLE CPU time donation system. and I struggled to design the database.
The problem is the following.
I have 3 entities (tables):
donors: hold donors' information
...
1
vote
0answers
26 views
Schema for managing house rentals
I'm desiging a database schema that manages house rentals
Below is what I have so far
property table
CREATE TABLE public.property
(
id bigint NOT NULL DEFAULT nextval('property_id_seq'::...
3
votes
0answers
41 views
Versioning rows in a table
So I have this concept in my head of "immutable" records, which I am sure is not novel, but can't seem to find the right google keywords.
Basically, historical row values need to be preserved as '...
0
votes
1answer
33 views
How to structure a nested database coming from JSON? [closed]
I am fairly new to SQL-based databases and recently installed MySQL.
This is my scenario:
I have a data server which stores user account data. Furthermore, there are many subservices that all have ...
0
votes
0answers
13 views
Restrict EDIT and DELETE actions on local database [closed]
in my current project the user setups an application locally on his machine and interacts without any internet connection. All his actions in the application are stored in a database. The problem with ...
1
vote
1answer
48 views
Database structure for a large amount of data with different types
Can I ask you for give me tips how to design efficient database to store large amounts of data with different types. I would like to use SQL Server 2016 as the database management system.
I have to ...
1
vote
1answer
29 views
How to handle removing ASP.NET users where their UserID is tied to rows that should be maintained even after a user is deleted?
I have a website that uses ASP.NET membership and so there are multiple aspnet_... tables in our database.
We are creating a service for the users that allow them to essentially create laboratory ...
-1
votes
1answer
21 views
ER diagram to Relational Schema (Many to Many relationship)
In this ER diagram, can anyone explain how we should convert the many to many relationships ('view', 'update' and 'diagnose') to Relational Schema and then SQL? What I think is that there shouldn't be ...
1
vote
0answers
36 views
How to design a database for production volume and loses control? [closed]
I must develop a database for inputting information of the produced volume (hourly), quality and technical issues during this production. There are 3 production lines. Each line can run through ...
1
vote
1answer
46 views
Need help with a table relationships and over-all design
My design is a Netflix clone which has movies and shows just like Netflix. There is also a table called transcodes which will have information on a movie or episode video transcode.
My problem is, I ...
0
votes
1answer
24 views
Invoices kept in document with user information in no sql database is recommended?
I have a application which has tenant details like
{
"name" : "",
"Address" : "" ,
...
Bills :[
current : {"rent":"" ,"electricity":"" ,"date":""},
previous : [{"rent":"" ,"electricity":"...
1
vote
1answer
42 views
One user affects all other users
I'm completely new to working with databases (other than retrieving data from them as a front end developer.) and have an idea I was toying with. However, I'm struggling to find exactly what to Google ...
0
votes
1answer
25 views
How do I draw the erd to model this scenario?
Customers are billed for the cost for the activity itself and also any "extra" items which are used as part of an activity.
So I have the customers entity (identified by customer id) and an item ...
0
votes
0answers
19 views
When do I create a separate entity when drawing conceptual models?
I have these 3 scenarios:
Rooms located in a house are assigned a room number within that house - thus, for example, each house has a room number 1. Each room has a phone number etc...
My guess is ...
0
votes
2answers
43 views
What is the difference between a foreign key and a primary foreign key?
I understand that you use foreign keys to relate rows from one table to the other and if this foreign key also becomes a primary key of that table then it is a primary foreign key.
But, for example, ...
0
votes
1answer
70 views
What is a good unique identifier for customer and payment details for this tourism company?
Given a tourism company, Air T&T, has customers. When a customer checks in to an AirTnT facility (i.e. house-share), AirTnT records the customer's first and last names, address, date of birth, ...
