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.

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
53 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
62 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
29 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
50 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
20 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
35 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
37 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
35 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
24 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
29 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
40 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
27 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 ...
2
votes
1answer
114 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
31 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
56 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
93 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
221 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
107 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
69 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
58 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
60 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
22 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
35 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
43 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
105 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
45 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
32 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 ...
-1
votes
1answer
58 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
42 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
53 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 ...
0
votes
1answer
36 views

MySQL schema design - how to decide between an `enum` vs. a table join

In designing a MySQL schema, should I avoid using an enum type altogether, as I have been reading really strong, but favored opinions on this subject, such as: Stackoverflow post on efficiency of ...
1
vote
0answers
66 views

Double associative entity in a business environment involving Order Item Reviews?

Suppose you have Item (item_id, item_name) and Order (order_id, user_id, order_date) entities, with primary keys denoted in bold. Because an order can consist of multiple items, and an item can be ...
0
votes
1answer
75 views

Is it good practice to create a separate table for views count?

I have created a blog table where I'm having a field called views_count but I have heard that updating the views_count field on each page view is strain. So I have created now a seperate table for ...
1
vote
0answers
20 views

Designing a message system

I want to create a messaging system but I'm stuck in the relationships between the sender/receiver of the message and the message itself. This is a list of requirements for this system: Allow both ...
1
vote
2answers
152 views

Data Warehouse: Auto Generate Slowly Changing Dimension Code For Any Table with Dynamic Sql

Can someone code review to take OLTP/Stage data and transform it into Data Warehouse Kimball Slowly Changing Dimension Type 2? This code should be dynamic, and should be able to utilize any table as ...
1
vote
0answers
44 views

User permission businness logic

This question will also be posted in the stackoverflow, since dba and programmers may have different opinions. First of all sorry for the long question, but I wrote some extra code so that I can ...
-1
votes
1answer
20 views

Creating blog fresh tables from old tables [closed]

I am re-creating a database structure from old tables. New tables should be optimized and easy to understand. In old database the developer used video table name for storing video blogs. However I ...
0
votes
0answers
50 views

Are there separate Object Oriented and Relational Databases in PostgreSQL?

I am new to ORDBMS and I want to know, can I have two types of databases in Postgres? - Object Oriented databases and Relational databases separately. Or there is only one kind of database which is ...
-1
votes
1answer
28 views

Storing User Information after Deletion

I am building a collaboration platform that focuses on being a knowledge base for teams and a big part of creating a knowledge base is storing historical information even when an employee has left. ...
0
votes
0answers
60 views

Kimball Modelling One Column Linking to Many Dimensions

We are conducting Quality inspections, Appraisals, and Ratings on furniture. Many different types of furniture exist, chairs (1), shelves (2), couches (3), table (4). The respective DimProductType is ...
4
votes
2answers
71 views

Should a composite primary key out of 3-4 attributes or a unique artificial ID should be used for the situation below?

For a small beginner project to start with databases and system development, I'm trying to develop a system that represents the classic case of an administration system for students and exams. This is ...
1
vote
0answers
26 views

2 sets of multivalued attributes

I have multivalue attribute which I need to store in 2 different tables. It represents qualificiations for certain jobs. One table is for workers profiles and another for company. Company table states ...
0
votes
2answers
65 views

Is the following database design good in the following table for the situation explained below [closed]

Someone has created this table with the following information: There are more entries in the database table which I have omitted for brevity purpose. My ultimate goal is to display the following in ...
2
votes
2answers
57 views

Is there an easy way to have a non-IDENTITY, auto incrementing column based on another column?

+----+-------------+-----------------+-------------+ | ID | ProductName | ProductCategory | ProductCode | +----+-------------+-----------------+-------------+ | 1 | Item 1 | 1 | ...