Stack Exchange Network

Stack Exchange network consists of 175 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
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [database-design]

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

0
votes
0answers
24 views

Mongodb - add new node secondary in existing replica set shard

my mongodb configuration which i am trying to add. systemLog: destination: file logAppend: true path: /var/log/mongodb/mongod.log storage: dbPath: /var/lib/mongo/mongo journal: enabled: ...
0
votes
0answers
18 views

mysql - Relational database design for language dictionary references (primary, secondary, tertiary) approach?

I have a project where I am making the Ancient Egyptian language dictionary. For this dictionary, besides words and translations, I want to have a reference for every entry, so the dictionary can be ...
0
votes
0answers
13 views

Modelling a school timetable database

I'm working on a design for a database for school timetable and I need some help. So, basically, the database is made up of tables like teacher, student, subject, TimePeriod, and Classroom. I would ...
0
votes
1answer
28 views

HashBytes Computed Column: Exclude Column

We want to create Hashbytes computed column Non-Persisted. Table has twenty columns, and would like to exclude IdentityId column. How could I do this in computed column? Is there a SQL library ...
0
votes
0answers
14 views

User Permissions - Department(s) and Sub Role(s)

Good afternoon, I am relatively newer to MySQL and I am looking for help to change a database structure up to allow more specific permissions based on sub roles. Currently, my database is only using ...
0
votes
0answers
33 views

Worked example of closure tables with from and thru dates

I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now. ...
-3
votes
1answer
40 views

Storage addresses in the database [duplicate]

I also learned that to get a list of tax rates for the territory I need to use the address and possibly a zip code. Is there a unified method for storing a structure of addresses in the database?
0
votes
1answer
29 views

Is there anyway to find the closest matching JSON entry inside a database

Scenario: suppose I have a list of numbers written in something like JSON stored in a database field. Something like this: ids{1,2,3} Let's suppose the database is filled with rows that each have a ...
0
votes
2answers
61 views

Dealing with subtypes and supertypes in Database design

I'm currently doing a database project for a class and I am having some difficulty dealing the subtypes and supertypes. This is the list of entities and their attributes: Piece (PIECE_ID, Description)...
2
votes
2answers
33 views

AdonisJS: is something wrong with belongsTo() relationship design?

In AdonisJs framework, suppose we have a Profile model in which we define belongsTo() relationship to theUser model. The documentation says this will lead us to define our databases this way: The ...
0
votes
0answers
25 views

Data model for handling time intervals with possible overlaps (booking system)

we are trying to make a booking system where supervisors can scedule halltimes (Halltime class), in which student assistants can make themselves available within the assigned halltimes (HalltimeTA). ...
0
votes
2answers
26 views

Column names - (created, modified) vs. (creation, modification) [closed]

I have a database schema where all the tables are required to have a column to hold the timestamp for the initial INSERT and another column that holds the timestamp of the last UPDATE. Initially this ...
0
votes
1answer
15 views

Linking tables in Access with unique ID

I am creating a database in MS Access that has 3 different tables with 3 separate types of information for one set of users, Personal, Business and Board. I have one table created with all the users ...
0
votes
0answers
34 views

Can an entity be an attribute of another entity?

For ER/EER Diagram: I have one entity Stadium and another entity Match. In Match entity I have the stadium as an attribute. The SOCCER database shall keep information about the stadiums used as ...
2
votes
1answer
42 views

Is using association tables for One-to-Many relationships common practice?

I'm currently running into a conceptual disagreement with some database architects at work. My education in this field is purely from the UofGoogle so I'm sure they're right, but can't find ...
1
vote
2answers
36 views

Way around Composite Primary Key requiring Composite Foreign Key

Here is a table called Make (where name is the PK) name|other columns ---------------------- A | ... B | ... C | ... And a table called Type (where make, name together is the PK and make ...
1
vote
1answer
61 views

Schema design for products with redundant values

so I was looking some design schemes for storing a Product's quantity of a certain color and variant sizes, @Gilbert Le Blanc gives the perfect scheme for me as answered in this question, and the ...
0
votes
0answers
23 views

Modelling a database for Employees Salary management

I have two types of Employees within my database. One which are salaried (permanent staff) and those who are paid at an hourly rate (hour based staff). Both can be assigned two different roles at the ...
2
votes
1answer
42 views

Make a DBMS-enforced multi-column “UNIQUE” constraint, with a “match all” wildcard

Well I wonder if I can do the following on database level (instead of server level). We are using PostgreSQL as the SQL flavour. I have multiple columns. Now I wish to make sure each entry into the ...
1
vote
1answer
58 views

How to relate Agencies at the State, County, and Local Government levels

I'm having a bit of trouble deciding how to properly relate government agencies to their respective state, county, and local government levels. I would like to be able to query for agencies that are ...
0
votes
0answers
24 views

Creating a relationship for a database model based on that model's date range

I have a database of Schedule objects in PSQL (schedule_id, user_id, date_range ( [date, date) ), and a few other fields not necessary for this discussion). Each of these Schedule objects is ...
0
votes
0answers
33 views

DB Structure and Search with weighted ratings

Let's assume the following structure. For each pair of item and category we have a rating (between 0 and 10) item_id | category_id | rating --------+-------------+------- 21 | 31 | ...
2
votes
0answers
31 views

Database Design for tracking user flow

I want to design a user tracking system between different tables/models. I want to track the user's progress. There is a Module table which is related to sections and sections are related to sub-...
0
votes
1answer
74 views

EER Diagram - subclasses which are neither disjoint nor fully overlapping

Suppose we are writing an enhanced entity-relationship diagram (EERD) for a company's database. This company offers two services A and B. If the two services were disjoint, then this might look ...
2
votes
0answers
23 views

How to handle normalizing the concept of a grid, and also plan for data versioning?

I have inherited a database that has a normalized structure, except for its data tables which are aggressively denormalized. Our system mostly renders grids into a browser, and the previous developer ...
1
vote
0answers
28 views

Modelling a database for a shopping list - should I represent the scenario with a “redundant” relationship?

[I may provide some pictures later] I'm facing this issue in modeling an entity-relationship diagram. I'll have a shopping list, where for each item the user inserts, it'll be associated with one ...
1
vote
2answers
43 views

How to implement a dynamic referral program?

Example: Currently, we will provide 100 credits each to both the referrer and referred. Then in the future, we might want to implement a different referral program. Such as change credits to 200 ...
1
vote
1answer
46 views

Setting up a many-to-many relationship based off of two other many-to-many relationships

I have 3 main top level entities: Project (PK: Id) Owner (PK: Id) Product (PK: Id) Here are the rules: A Project can have many Owners. An Owner can have many Projects. A Project can have many ...
1
vote
0answers
17 views

Design table that solve a service payment or a package service payment?

I am learning and practicing some database designing and case study on Selling Services on Internet. In my requirement, user can buy a post service and add more extra option if they need. In fact, ...
1
vote
0answers
37 views

Database design for multiple units of meassurement and pricings for each item/product

Table Products id_product| name | .... 1 | abcd | .... --- Table Pricing id_price | qty | price_type | price 1 | 20 | discount | 20 --- Table Unit id_unit | ...
3
votes
1answer
42 views

Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes

I am designing a system within which I will need to be able to search a database using a file or string SHA3-256 hash. For example the hash of test is ...
0
votes
0answers
32 views

Reducing the number of tables used - mySQL

I have a table for tax implementation in my application, but I am not sure the design I have done for the table. Can someone review it? val_taxes is the table which has all types of tax listed in ...
1
vote
1answer
20 views

How to specify the domain of an attribute in an entity-relationship diagram (ERD)?

In an ERD (as in, rectangles for entity types, diamonds for relationship types, ovals for attributes, etc.) how would one specify that an attribute is, say, a 5-letter word?
-1
votes
2answers
44 views

Table Structure for Storing multiple foreign keys

I have two tables says, val_taxes and val_tax_groups the first table has all the tax separately like so val_taxes | id | tax_name | tax_value | ----------------------------- | 1 | sgst | 2.4 ...
0
votes
1answer
38 views

Database design - repeating values

I have an application that deals with businesses and their physical locations. So most tables have a location_id field that logically partitions the data. One table deals with marketing referrals and ...
0
votes
0answers
29 views

How to Represent a VARCHAR Column with XML Data in a Fact Table?

I have SQL Server 2016 environment and I have decided to design a source table with holds some sort of event data as a transactional fact table. The table has about a million rows, and generates about ...
0
votes
2answers
53 views

multiple ids table for accounting schema

I am trying to make a small accounting db and I have hit a dead end, my search bared no results as of how to make this idea work. In the picture below you can see that I am trying to link multiple ...
0
votes
0answers
25 views

Database design to store user relationships

My DB model is shown in the picture. Does this design look good? I welcome comments and ideas. Description: User role table keeps track of all possible roles a user can have Role table has all ...
1
vote
1answer
25 views

When creating a many to many relation with an attribute, should I include a non-composite primary key?

For example, I have frame and voltage tables, both of which relate to a motor object: frame(id, motor_attribute); voltage(id, voltage_attribute); I can pick a frame and pick a voltage and for each ...
4
votes
1answer
84 views

Is it better to have a wider single table or “child” tables based on business processes?

The example I am working with is an invoice data model, where there may be around 30 columns by the time we're done designing. Each row of data would be unique to a single invoice. Some of the ...
9
votes
2answers
669 views

Is a table without a primary key normalized?

In a lecture, my lecturer showed us a table without a primary key. Upon questioning, he said that in 3NF when you remove transitive dependencies it's ok to then have a table without a primary key. ...
0
votes
1answer
52 views

Appropriate column type(s) for dates and dynamic pricing data

What is the appropriate column(s) type(s) (trying to be database agnostic, but realize that could play into the answer) to store a months worth of pricing data (i.e. could be the daily hotel rate, etc....
5
votes
4answers
457 views

Which columns should be indexed when all may be used in different search queries?

Background I'm working on a website for a movie theather chain currently located in four different cities (might expand in the future). They use the same, single-database website for all cities, ...
0
votes
0answers
22 views

Dance/martial arts school ERD asssessment

This is an ERD I've put together as the initial structure for a martial arts or dance type school. One person could have multiple schools, each school multiple students, etc. One schedule per school. ...
0
votes
0answers
29 views

Advice Regarding Database Design

I am working on a poory designed application, so please do not judge me by the work i have been posed. I have servers table whose data can be visualised as and attributes table which can be ...
2
votes
1answer
64 views

Questioning a DB Consultant and their recommendations

We have hired a systems design consultant to help use architect a new CRM system, bringing together several business needs into one system, instead of the several we currently have. It's the typical ...
0
votes
0answers
29 views

How to improve query performance through denormalization of this schema or other techniques?

My database attempts to describe some items, their sources and a variety of restrictions on who may use them as well as a number of intrinsic properties about the items. It looks a little bit like: ...
0
votes
0answers
50 views

Syntax to establish a strong and weak entity relationship in MySQL? Weak entity's partial key is different from strong entity's primary key

The following image below depicts an entity-relationship diagram (ERD) of a strong and weak entity. The closest example, I found here (link), required that a foreign key be defined in the weak entity,...
0
votes
0answers
19 views

Permissions and/or roles for Student Marks Database

I am creating a student marks database, but am wondering how best to handle permissions. The current setup Currently administrators, instructors, and students are all in their own tables. In the ...
1
vote
1answer
112 views

JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where all column data ...