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
1
vote
0answers
20 views

Referential integrity based on data more than a table away

Notes on tables A, B, C, D, E B.aid and C.aid reference A.aid. D.cid references C.cid and D.did is the primary key. D is a C. cid and did are pre-assigned and I cannot change them (they need to ...
0
votes
1answer
21 views

How to map single-products to categories in my scenario?

I have the tables depicted in the following diagram: Let's say I have only two products in my database at moment, one that has variants, while the other does not have them. Now my question is: how ...
3
votes
2answers
86 views

Database design that requires an index over two tables

Using PostgreSQL (currently 9.6, but upgrades are possible), I currently have the following database layout where customers can order products, which are themselves sorted into categories (products ...
0
votes
1answer
28 views

Better solution for creating many-to-many table per entity

I have a table for images. I have tables for entities such as people, cars, houses, trains and dogs to which I need to assign images. I need to assign images to entities. The obvious solution is to ...
2
votes
2answers
52 views

Define JSON Index for Schemaless using MySQL 8.0

We are using MySQL 8.0 and need to implement Schemaless Feature where we allow Vendors to create a Module(ObjectName) and add columns(int, string, currency) to it. Vendor can add objects after ...
1
vote
1answer
62 views

Should address be in a separate table?

I have a database that is for managing rental properties It has a property table that has details about the property such as bedrooms, sq feet, price, etc, it also has a column called address_id ...
1
vote
1answer
71 views

How to properly implement relationship between a user and multiple other users [duplicate]

I have a table with the following structure: Table_1 --- UserId (int) --- UserName (varchar) A user can have a relation with other users. Meaning, a UserId from Table_1 can have a relation with ...
0
votes
1answer
33 views

Appropriate situation to data warehouse and how to best manage the warehouse? [closed]

I have a query that is on the opening page of our application that takes about 8-10 seconds to load. I've tried all I can to optimize it and I simply cannot make it go any faster (indexed ...
2
votes
2answers
59 views

Does the relationship between teams and matches have a many-to-many cardinality ratio?

Diagram I created the following diagram: Considerations So I will create two tables, one called Match and the other one called Teams. In Match there will be data about two teams, Away and Home. ...
-2
votes
1answer
45 views

Is MongoDB an appropriate choice for storing large files as compared to MySQL? [closed]

I'm working on a project to store large JSON files (~1 MB each) in a database, with 10 of these files linked to one user profile. Normally I would use MySQL, and have two tables, one with the meta ...
16
votes
2answers
1k views

Using column size much larger than necessary

I'm creating an SQL Server database with someone else. One of the tables is small (6 rows) with data that will probably remain constant. There is a remote possibility that a new row will be added. ...
0
votes
0answers
32 views

MySQL not using index. Working fine with FORCE INDEX

This is my table schema along with defined indexes: CREATE TABLE `chat_state_event` ( `id` bigint(20) NOT NULL, `agentId` varchar(255) DEFAULT NULL, `allocState` varchar(255) NOT NULL, `...
0
votes
1answer
33 views

Schema for stores and groups of stores and companies

Perhaps a silly question. I am building a database where I keep a number of stores in a Store table. Each store obviously have some data as geo coordinates, address etc. Right now these stores are ...
0
votes
1answer
31 views

When normalising, if you have entities with the same primary key, does it mean it should be the same entity?

Crrently I have this check in data where each check in has check-in details, activities and total extra items charge(extra items are required for some activities). In UNF, I separated them into 3 ...
0
votes
0answers
25 views

How to effeciently save this kind of text into database?

I have this kind of text and want to save it to database: I. Master Text I <<<Parent I.A. Sub Master Text I &...
1
vote
1answer
49 views

Storing numbers that will be also used as digits

I'm new to databases, please help me to figure out the best design to store 8-digit numbers that are going to be used for two purposes: Statistical analyses of separate digits. For example all 2nd ...
0
votes
0answers
30 views

Storing the way a field value(s) is(are) stored in the database

Context I have tables in which some fields can be edited by a user through a form. The form is presented as an information sheet about a person : some fields are read-only, others are editable, some ...
0
votes
2answers
31 views

should parent entity always have a primary key?

I am have 3 entities, Person(fname, lname) none of them is primary, then User(email, password) and Receptionist(emp_id) bold is primary key. I have extended there two entities from Person, total ...
0
votes
0answers
68 views

Building an Entity-Relationship Diagram for a Language Dictionary

I need advice on how to build entity-relationship diagram (ERD) for Language Dictionary. Since I did not build ERD before, I will try to explain and show the ultimate goal of this project below. ...
0
votes
0answers
22 views

How to model this scenario in conceptual erd?Bridge entity needed?

I have a house-share entity( primary key : house-share id) and housekeeper entity (primary key: housekeeper id). At any point in time a housekeeper can only work in one house-share. When a ...
0
votes
3answers
54 views

Yes/No columns in logical database design

I have an assignment where I have to design a logical model. I have a relation NURSE that has "nurse_id" and "certification". The certification attribute has yes/no values, so, should I move it to ...
0
votes
1answer
25 views

How to add entries in a table column as the attributes for another table in MySQL? [closed]

I am designing an Attendance Management System, so for the table which contains the attendance status(Present or not) of all the students for all the days and all courses, following are the details: ...
0
votes
1answer
23 views

How to design a persistent table with dependencies which are allowed to be deleted in the future?

I am designing a database that archives all class schedules and student attendances based on these schedules. So here are the tables. Teacher, Subject, Classroom, Class Schedule table has many-to-...
1
vote
1answer
38 views

How to avoid losing information via UPDATEs due to database design issues

Business requirements I have a database where information about documents and their related packages are kept. Each document can have multiple packages, and each package has its details and product ...
0
votes
0answers
16 views

Zero-to-many cardinality ratio on both sides of a relationship

Are there any use cases for 0..n to 0..n relationships? Of the cases I can think of, 0 cardinality on just one side seems sufficient and less confusing. For example, 0 or more employees have 0 or ...
0
votes
0answers
16 views

Is it useful to have a 0..1 to 0..N here?

I came across this example from school. Doesn't this imply that you could have something like: 0 messages are answer to 0 messages? It confuses me, because wouldn't a 1 to 0 or more messages suffice?
0
votes
0answers
34 views

Does having unused columns effect the indexing of the table

I have a table where there are some columns which are empty and they are not used. That table has to be indexed multiple times based on the application. Does having these empty columns in my table ...
0
votes
0answers
18 views

How to design a database with product-dependent columns? [duplicate]

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. ...
-1
votes
3answers
55 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 ...
0
votes
1answer
38 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 (...
2
votes
1answer
39 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-...
-1
votes
1answer
19 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
28 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 ...
0
votes
1answer
37 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
1answer
463 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
28 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 ...
-1
votes
1answer
42 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 ...
1
vote
1answer
54 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 ...
2
votes
1answer
45 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
55 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 ...
0
votes
3answers
291 views

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
100 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
35 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
21 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
19 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
24 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 ...
0
votes
1answer
34 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
28 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 ...
0
votes
2answers
30 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
1answer
47 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 ...