Stack Exchange Network

Stack Exchange network consists of 174 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

Questions tagged [database-design]

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

1
vote
1answer
34 views

Can you get multiple values from a table into one row?

I'm extremely green at database design and have been given a project to complete as part of an assignment. My question is, in regards to values from a table, in this instance, it relates to health ...
-1
votes
1answer
21 views

The fastest way to Copy Schema or Table in MySQL DB

I have a schema (size 500 GB) in my MySQL DB and I want to make a copy of this schema to another schema in the same DB. May I ask what is the fastest way of copying all the tables in that schema? (I ...
0
votes
2answers
57 views

Most performant design for each member to manage his/her own news list?

Say a news website has news categories such as Politics, Business, Society, Education, Health, Tech, Celebrity, Lifestyle, ... etc. The number of categories may be more than 20. New categories may be ...
1
vote
2answers
124 views

Why is data integrity not possible when using denormalization?

This tutorial says the following: For proponents of denormalization, the thinking is as follows: normalization creates more tables as you proceed towards higher normal forms, but more tables ...
0
votes
0answers
25 views

Specifying primary keys, and designing relationships for my data

I have this database I want to use as a backend for a teacher resource database. The columns in question are as follows: In this table, a class has one subject, one course number, and one section. ...
0
votes
2answers
52 views

First names database design

I have a large amount of data on users (about 8 million users). I want to mine this data to create a table with statistics about first names. It should also include as many variations of each name as ...
0
votes
1answer
26 views

Am I on the right track for a customer user login database design?

I am designing a back end database that will handle registration and login. I need to be able to deal with different account and authentication types. The main issue here, that is different from ...
0
votes
1answer
17 views

Need the name value of the enterprise to be the same as it was when it was registered and not the value it currently has

I will explain the problem with an example: I am designing a specific case of referential integrity in a table. In the logical design there are two tables, enterprise and document. We register the ...
-2
votes
0answers
19 views

Variable length Work Histories in a RDB [closed]

I am building a database for CVs/Job Applicants in Postgres (via Django), which would naturally include work histories for every person. Have to consider the variable length of people's careers, ...
-2
votes
1answer
31 views

Db design help for Mobile device db in mysql [closed]

I have a database with 5 tables: device_company device_series device_model device_type device_data device_data will contain the id of all the above tables. I am wondering whether this is the best ...
-1
votes
1answer
33 views

What are the advantages and disadvantages of using a embedded sql database? [closed]

What are the advantages and disadvantages of using an embedded SQL database instead of MySQL server? I am a web developer and I'm planning to develop an application for the shop, without investing my ...
1
vote
0answers
20 views

Db Design: Junction table or column

I have three tables like below. user company user_company -------- ------- ------------------ id(pk) id(pk) user_id(pk, fk) company_id(pk, fk) My ...
0
votes
2answers
49 views

Modelling two dependent many-to-many relationships

I have a user who may have 0 or more foo's and bar's. Here is a diagram: Each foo has a unique integer ord. If a user has a bar, then this implies that he has a foo with ord=1. A user may not have ...
-2
votes
1answer
34 views

Is it efficient to design chat/likes/relationship database in separate table per user? [closed]

The specs: there would be about 1 billion users there would be probably 1 million topics each topics has subtopics stored at the same level as topics user has many to many relationship with topics (...
2
votes
0answers
38 views

How To Query Databases in Subject-Predicate Format?

I have csv data in this format: id fieldname fieldvalue -- --------- ---------- 1 fieldA A1 1 fieldB B1 2 fieldA A2 2 fieldA B2 If I upload this to MySQL, how can I ...
0
votes
0answers
23 views

How do I force a selection of items to a specific user/database row?

I have a database that allows a company to register, then the user of the company can register individual "user" accounts. The company administrator (who sets up the company account) can log in with ...
0
votes
0answers
21 views

is there Better way to do this kind database schema design, mysql

P.S. Hopefully I am not posting in the wrong place. If I am, please let me know where to post. I am currently using laravel with query builder instead of eloquent for a small self project. I am ...
0
votes
3answers
93 views

Model multiple 1-m relationship to a single entity

I have an entity called Box. This box has a from property which could be from a variety of different entities. It could be from a user, office, import, storage and many more. So far I can think of ...
1
vote
1answer
20 views

Subset of Unordered Pairs of Distinct Primary Keys With Normalization

I am currently in the process of designing a database. I have one table as follows: CREATE TABLE Set ( id INT PRIMARY KEY AUTO_INCREMENT, -- other values ); Now I wish to create another ...
0
votes
2answers
46 views

A table with many null values VS many tables

Basically my level of DB knowledge enables me to create, select, delete and some simple things. When it comes to designing for performance, I have absolutely 0 knowledge. So my question here is let ...
1
vote
0answers
29 views

Basic company and user registration database - Pulling my hair out

I have now created a partially working database, although I feel I have over complicated things, and also, not sure where to add the extra tables/relationships required. First off all, I have a ...
0
votes
2answers
47 views

Postgres jsonb vs composite type performance differences

What considerations are involved in choosing between a jsonb column and a composite type column of the same structure? For example, consider a column like that used in the Postgres documentation: ...
1
vote
2answers
63 views

Poor SQL Server Performance With Nested “OR” Conditions In LEFT OUTER JOIN

I have a tricky bit of SQL that's giving me a bit of trouble. I'd like to introduce several new columns to a key table whose components may or may not be used based on user configuration (Dimension's ...
1
vote
1answer
33 views

Converting (a) an entity-relationship diagram about an airline booking scenario to (b) a relational schema

I am currently following notes on a certain "7 step strategy" to transform an ERD to a relational schema and was wondering some points about step 4, converting one-to-many (1:N) relationships. The ...
1
vote
0answers
20 views

Cardinality with specialization in enhanced entity-relationship diagrams

I'm currently taking an introduction course to databases and I've come across a problem not described in the textbook. I'm wondering about the use of cardinality with the use of specialization in ...
0
votes
0answers
33 views

Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design?

I have a functionality that has multiple (7+) potential properties to be added via a central 'hub' table. In most cases there will only be one property assigned, but it's possible the user will want ...
0
votes
1answer
49 views

Ensuring unique value for group of rows in table with trigger and concurrent transactions

I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a ...
4
votes
0answers
59 views

Which data model/schema to apply to a timeseries datastore for data sources with varying fields

I'm asked to develop a data-storage for time-series data, yet despite significant research I'm unsure about the data model and storage technology to chose. About the data The source data which is ...
2
votes
2answers
52 views

Which database management system type to choose for price tracking? [closed]

I'm writing a price tracking application. Every day I need to store price information about ~15M products. (So it's about 15M * 365 = 5.5B records per year). One product has many prices over ...
0
votes
0answers
24 views

Need an efficient mysql database schema to store points/ratings

I have four main tables- 1.user(user_id, name,...) 2. post(post_id, description, ...) 3.comment(comment_id,user_id,comment,...) 4.reply(reply_id,user_id, reply,...) and 5.user_post(user_id,...
1
vote
1answer
57 views

SQL Database structure to store calculations

I am in the process of designing a database structure to store some calculation information. Information about the problem: Each "calculation" is a collection of scripts allowing to calculate value, ...
0
votes
0answers
37 views

[MongoDB]See Collections (and Files) created in a Shard on the Cluster

My structure: A Sharded Cluster with multiple Shards. Each Shard contains 1 Collection, which is pinned to it by tags. Every Shard is at the same time a ReplicaSet Secondary. It receives data from a ...
1
vote
0answers
16 views

Best practise for data dump 'entry point' in DB processing chain - PostgreSQL

I need to implement a process chain to transform and analyze potentially large sets of data, inserted both programmatically and manually (e.g. CSVs). In either way, the input data is standardized in ...
0
votes
1answer
25 views

Uncertainty about number format in MySQL

In Oracle Database, fields like this: example number(6,4) not null; Indicate that I'm reserving 6 digits for this column, 4 that are being used from the point to the right and 2 for the point ...
0
votes
0answers
17 views

Database Design to Store Events Which Have Different Required Attributes

I am building a billing database for my office. I am planning to write a set of queries to determine time overlaps because my users get billings per minute. In order to do this each user will have a ...
0
votes
1answer
44 views

Is it Possible to have a database table table of morethan 3000 fields [closed]

I have a form of more than 3000 fields and i want to create a table without splitting it into two. Is it okay? Or it will have problems?
0
votes
1answer
47 views

Database Design - multiple tables

I am trying to build a database that has all of our employees, their skills, and the level of their skills. The idea is that a manager could go in, type in a skill, and relevant employees are returned....
0
votes
2answers
36 views

How to model very flexible referencing relationships between many tables?

I have various different entity types in my database. For purposes of example, consider an IMDB database: Movie Person Actor Director Translation and 20 more tables My application enables free-form ...
1
vote
1answer
22 views

redesign database around selecting rows by time range

I currently have a MySQL database that grows about 50,000 rows a day. When querying data out of the database, I query information from the info table and use a subquery on the session table to refine ...
1
vote
2answers
40 views

database design for song position under play-list

I have a music streaming application where I have playlists. Each playlist has maximum 100 songs. I have a mapping table called PlaylistSongMapping which has the following schema +------+------------+...
0
votes
1answer
39 views

Table with only one column because of many-to-many relationship?

Right now I have three tables: person, alias, and person_alias. As suggested by the names of tables, one person can have multiple aliases and one alias can be used by many people, and hence I add a ...
1
vote
1answer
55 views

Hierarchical / tree data - which type of database to use & design?

For querying (and writing to) a hierarchical structure (like a folder containing files and other folders, up to n levels deep, where n is pretty small, probably around 10, and there would be lots of ...
1
vote
0answers
28 views

How can I store data with unpredictable data types in a table?

I'm currently working on a Headless CMS project and was wondering what approach would be the best for storing "fields" data in the database. Basically, users would be able to create a content model ...
-3
votes
1answer
26 views

Database design for multiple tenant application

I am building a web application where we have the concept of groups. A group can be roughly thought of like a Facebook group where you have a number of people attached to it. The DB for the app is ...
0
votes
1answer
87 views

Conversion failed when converting date or time from character string error?

i have a problem with this error i don't know why but i try many thing and search about it bout i didn't find any thing to solve this problem ( i want to make a query to calculate the age for the ...
1
vote
1answer
33 views

Should I create seperate referenced table or go with serialized data in a column?

I have a table with name app_client with the below fields id, user_id, name, age, phone, gender, related_entities_json (long text datatype), created_at, updated_at I have 200k+ records in this ...
5
votes
3answers
166 views

No Primary Key in some sql server tables

I have downloaded WorldWideImporters Database as a standard db (Microsoft Sample Database for SQL Server). In this db, some of the tables don't have primary keys but clustered indexes can be seen. Can ...
0
votes
1answer
27 views

Why the Accounting DB Schemas do not allow to maintain a clean Products Table?

As far as I know, the database schema of the vast majority of Accounting Systems follows this architecture: Considering all the FKs, I know that the product table is going to grow in time and, if I ...
0
votes
0answers
44 views

Database Design - multiple table for different types of transactions

Considering a trading system as an example. Would it make sense to have two different tables for credit transactions and asset transactions? creditTrans : table id userId transType status amount ...
-1
votes
1answer
41 views

Design with new table and old tables

I have to add a new table according to some new requirements, the model currently consists of two tables: DETAIL and SUMMARY. The relation is that every detail has associated one summary, so now I ...