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
1answer
50 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
23 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
46 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
40 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
75 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
37 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
29 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
404 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
34 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 ...
6
votes
3answers
183 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
57 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
64 views

Multiple tables 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
42 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 ...
2
votes
1answer
31 views

Prevent a user from making changes to some columns in a MS SQL Server database

I'd like to prevent a user from making changes to some columns in a MS SQL Server database. I cannot lock the column as READ ONLY as there will be changes made to that column. Let me explain as best ...
0
votes
1answer
59 views

Designing a data model for a survey; building the relationships

Let's say I have a survey that will be handed out to many people. I have a hypothetical diagram with User, Question, and Response entities. Should my relationship be one-to-one from question to answer?...
2
votes
0answers
63 views

Need help designing golf pro scheduling into POS [closed]

I have an existing Point of Sale app that is used for golf courses. I have a new feature request from one of my courses that would allow users of my POS to sell golf lessons to customers. Here are ...
0
votes
0answers
17 views

Saving records in MySQL that does not pre-exist

I have asked this question on SO but believe this is the right forum to ask, so here it is Let say, I have a pre-defined table called cities, with almost all the cities in my country. When a user ...
0
votes
2answers
31 views

How to model a relationship with different roles of users

I am creating a new project and currently modeling the relevant business domain. I am hesitating to choose one solution over another. Consider the following case. There is an order, the order has a ...
0
votes
1answer
51 views

Designing a graph like DB

In my case, I have 5 directions: 1:North, 2:South, 3:East, 4:West, 0:Unknown. A user can stand in one direction of another user. If he is not standing in any direction, he will be considered in the ...
0
votes
1answer
23 views

Table Relationships for Property Bags Configuration

I am creating a database table setup for user preferences in my application based partially on this design and while I created the tables and foreign keys, I can't seem to figure out the relationships ...
2
votes
1answer
37 views

One vs. Many Tables for Frequently but Asynchronously Updated Columns?

As part of my Postgres 10 schema, I have two tables; current and historic -- both have the same columns. current has about 20 (+/-) columns, and holds the most up-to-date information for accounts. I ...
0
votes
2answers
39 views

Is there a way to automatically update a view based on daily created tables?

Let's say I have a table that is created daily: 20181227 20181226 20181225 ... There is view that is like: CREATE OR REPLACE VIEW someview AS SELECT * FROM 20181227 UNION SELECT * FROM 20181226 ...
0
votes
1answer
36 views

Implementation of an employee and customer scenario in database

So, I have a scenario of a movie theatre and in this scenario employee can be a customer too... if I create two entities, customer and employee ... and make emp_id of employee a foreign key in ...
0
votes
0answers
28 views

Create new table with unique foreign key or Nullable columns for one to zero/one relation? Better design?

I am newer to database design, and was hoping I could get some input on a concept I have not seen that much discussion on when I look it up. I am using Postgres 10. Basically, I have a User table ...
1
vote
0answers
61 views

help in designing a database for medical clinic keeping record of patient visit

I am making software for patients record keeping in a clinic . I have written a peewee model for setting up tables to store record of each patient visit and patient contact and a relative table that ...
0
votes
0answers
30 views

Set up table for relationship type with both users in a different visitor table

I am a newbie in database driven application, and I just started working on a web app for my brother and parents clinic to manage patient records. So for their clinic I have one table Visitor that ...
2
votes
1answer
43 views

Is a functional dependency on a “child” table allowed in third normal form (3NF)?

Consider two simple tables, OrderHeader and OrderDetail which contain, respectively, the data shown below: OrderHeader | OrderHeaderID | CustomerID | OrderTotal | |---------------|------------|------...
0
votes
0answers
28 views

Database design of beauty center

I'm planning a database to manage the reservations of a beauty center. I'm having some trouble creating his conceptual scheme. I have 4 customers, booking, beautician and room. customers can book a ...
2
votes
1answer
48 views

How to store data that depends on dynamic number of parameters

I can't get my head around this problem. I'm trying to find a good way to store this in a PostgreSQL database. Each client of my company needs to visualize data that depends on a dynamic number of ...
1
vote
1answer
120 views

Microsoft Optimal method for columns into 1 word: XML Raw or Concat

What is fastest, performance method to make columns into one word? Does Microsoft have recommendation, or is there performance Big-O studies on FOR XML RAW vs Concat? Company has multiple tables with ...
0
votes
0answers
20 views

Github version for PostgreSQL query release [duplicate]

I have a big problem in my office environment for releasing query of postgresql. For example, there are position for developing Login, Payment, and Logistic. Login developer requests to change some ...
0
votes
2answers
30 views

Two type users configuration

I am developing an app that has two types of user: Customers and employees. I have planned to make the supertype as user that contains only: id username password While there would be two subtypes (...
1
vote
0answers
108 views

Interesting Database Designs for Product Catalogue including Recursive

I am doing some work for a client and they are creating a database of products of which I need to consume product data when it's created and updated to store in a third party service and then present ...
0
votes
1answer
75 views

Designing a database of Recipes

Task I need database design to record Recipes. Recipes can contain several Ingredients and several ordered HowToSteps. Each HowToStep can be split into ordered HowToSections. A single recipe can ...
2
votes
1answer
106 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
2
votes
3answers
336 views

Insert self referencing entry into SQL Server

I have a Person table that has a created_by column that references the primary id of the table itself. So, it could be an employee that adds another employee to the database. It works fine. But ...
1
vote
1answer
321 views

Does it make sense to create an index on foreign key (uuid) column in Postgres?

Assuming 2 sample tables in Postgres 10.X: CREATE TABLE public.order ( id VARCHAR(36) NOT NULL, ... ) CREATE TABLE public.suborder ( id VARCHAR(36) NOT NULL, order_id VARCHAR(36) NOT ...
0
votes
1answer
71 views

single or multiple databases advice needed

I know there are a few of these posts, but not in relation to my specific quandary. I've always gone the 'per user' SQL Server DB rather than 'one DB for all' because: a) I thought speed wise it's ...
2
votes
0answers
63 views

Non-standard number data types in Oracle

I'm not sure that this question belongs to this StackExchange. I've asked this question on StackOverflow: Besides the "usual" number data type where precision is greater then scale there are ...
1
vote
1answer
69 views

Getting attended, absent and no class days from database attendance

Problem the problem is that I am able to get the days instructors record attendance and only that, I am unable to get the days they didn't make attendance and the days the instructors were not ...
0
votes
1answer
27 views

SQLite how to store translations

Im looking for advise because my experience is not really good in SQLite. My table look like this: CREATE TABLE IF NOT EXISTS Route ( id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL, name ...
0
votes
1answer
48 views

SQL schema for movie rating and seenlist

I'm making funny app. User can mark movie as seen. User can rate movie (1-10). When user rate movie it is automatically marks as seen. User can't remove movie from seenlist if movie is rated by him. ...
0
votes
1answer
45 views

Correct Model for Users in a Collection with Items in that Collection

Sorry for my vague title, but I didn't really find how to succinctly formulate my issue. I have the following story I want to model: There are Users that belong to a certain Group. These Users can ...
1
vote
2answers
175 views

Is NEWSEQUENTIALID unique within a database for Many tables?

Is NEWSEQUENTIALID unique within a database? We plan many tables in a database with NEWSEQUENTIALID, inserting simultaneously and want to ensure every guid will be unique, regardless if they are in ...
0
votes
1answer
50 views

Entity-Relationship Diagram problem: trying to resolve a tentative many to many relationship

The case study states "each car belongs to a car type" and "each mechanic specializes in a car type or many car types". My current entity type definitions: Car CarType Mechanic My Entity-...
-1
votes
1answer
34 views

the difference between customer address table and address table

I was having a look at this data model to get more acknowledged for designing my own. Could someone kindly tell me what I need the Customer_Address table for? why don't we just settle with the ...
0
votes
1answer
86 views

How to maintain customer addresses for Orders?

The database I am designing is part of a project I am working on. I have come up with 3 simple "business rules": Customers must either be Individuals or Businesses (a business belongs to a certain ...
0
votes
1answer
43 views

Vehicle Pickup time database design

The objective of this is to give workshops the ability to give their customers a choice of vehicle pick-up times and likewise customers having the ability to choose their preferred pick-up time. I ...
0
votes
1answer
56 views

Create Copy of all rows where foreign key matches specified and then replace foreign key

I'm new to database administration (this is my first time) and have been tasked with creating a database with functionality that allows users to select a "Model" as a current state which can then be ...