---
esco_uri: http://data.europa.eu/esco/skill/6c08403c-a5bb-4868-b8c2-b7d039c0e511
esco_label: "design database scheme"
relation: essential
onet_soc: 15-2051.00
source: stackexchange
confidence: high
qa_count: 5
generator: gemma3:27b (prompt-designed and spot-checked by Claude)
generated: 2026-07-10
---

# design database scheme — Data Scientist

As a Data Scientist, 'designing database schemes' isn’t about being a DBA – it’s about structuring data *for analysis*. You'll frequently be tasked with creating schemas to ingest and store data from diverse sources (APIs, logs, files) for modeling. This often means quickly prototyping relational databases in tools like PostgreSQL or MySQL, but increasingly also involves NoSQL solutions like MongoDB depending on the data structure and scale. A typical task is taking a messy CSV or JSON feed and transforming it into normalized tables optimized for querying with SQL or Pandas/Spark. You're not building production systems necessarily, but *preparing* data for them.

## Praxiswissen (Stack Exchange, CC-BY-SA 4.0)

**The user is deciding whether to store user-uploaded files (1-10MB) directly within their MySQL database or store them on the filesystem and reference them with paths in the database, concerned about performance impacts.**

While storing files *in* a database offers transactional consistency and simplifies backups, it significantly increases database size and maintenance complexity. Larger databases require more resources, specialized knowledge to manage, and can hinder portability. Storing files on the filesystem avoids these issues, offering simpler access and reducing database overhead, but requires careful management of file synchronization and potential orphan records.

*Source: [Is it a bad practice to store large files (10 MB) in a database?](https://softwareengineering.stackexchange.com/q/150669) — answer by Thomas, CC-BY-SA 4.0*

**The user is designing a database for a task assignment system and struggling with how to represent many-to-many relationships between people and tasks without creating unwieldy table structures with numerous redundant columns.**

Instead of storing lists of IDs within tables or adding excessive columns, the best practice is to create a separate 'junction' or 'linking' table. This new table establishes explicit relationships by referencing primary keys from both related tables, adhering to database normalization principles. This approach allows for flexible and scalable many-to-many associations without data duplication or management overhead.

*Source: [Is it ever okay to use lists in a relational database?](https://softwareengineering.stackexchange.com/q/381460) — answer by whatsisname, CC-BY-SA 4.0*

**The user needs an efficient way to store a reorderable list (wishlist) in a Postgres database for a large user base, avoiding performance issues when items are rearranged.**

Instead of using sequential integer indexes that require updating many rows upon reordering, leverage string-based or sparsely populated integer indexing. This approach avoids the need to shift all subsequent item positions during drag-and-drop operations by providing ample 'space' between index values. The key is to design an indexing system where inserting a new position doesn’t necessitate modifying existing records.

*Source: [Storing a re-orderable list in a database](https://softwareengineering.stackexchange.com/q/195308) — answer by Alexander Bird, CC-BY-SA 4.0*

**The asker is considering using an Entity-Attribute-Value (EAV) data model for product features that vary significantly between items, but is concerned about its reputation as a design anti-pattern.**

While EAV offers flexibility to adapt to changing or diverse data requirements, it introduces significant performance risks and complexity. It's best avoided unless absolutely necessary because the potential for misuse and difficulty in querying outweighs the benefits, especially considering future maintainability by less experienced developers. Prioritize simplicity and well-defined schemas whenever possible.

*Source: [EAV - is it really bad in all scenarios?](https://softwareengineering.stackexchange.com/q/93124) — answer by maple_shaft, CC-BY-SA 4.0*

**The questioner is concerned about minimizing the number of tables in a database design and wonders if fewer tables are inherently better.**

Database design should prioritize correctness and functionality over simply reducing table count. The optimal number of tables depends on the specific data relationships and requirements; focusing solely on minimization can lead to poor structure. Don't worry about having 'too many' tables unless there’s a very unusual technical constraint, and consider denormalization only when performance issues arise.

*Source: [Is it necessary to create a database with as few tables as possible](https://softwareengineering.stackexchange.com/q/83553) — answer by FrustratedWithFormsDesigner, CC-BY-SA 4.0*

## Weitere Anreicherung

Stage-2 source for future practitioner grounding: arXiv cs.SE (software-engineering preprints).
