Questions tagged [database-design]

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

Filter by
Sorted by
Tagged with
0
votes
2answers
28 views

Is there a good reason to put the company name at the beginning of the table names? [on hold]

When developing a new system, for a big corporation. Technologies: Web, Java, Spring Boot, Rest Api, Postgres, Hibernate Company: Bank System: Promissory control, and all others Company name: ...
1
vote
1answer
20 views

Database design for bidirectional relationship

I am modeling a bidirectional relationship between users with a relationship table. Each relationship deals with two and only two users. Each direction of the relationship allows for 3 different ...
0
votes
0answers
11 views

P2P message system design

Database noob here, just curious to see if my design could benefit from any improvements. My goal was to keep this as simple as possible: a user can create an account, two accounts can message each ...
0
votes
1answer
124 views

Setting up relationships properly with the Access UI

I am making the database design of my website in Microsoft Access 2013 because it has a nice user interface and I believe it will help me understand everything a whole lot better with the UI. I have ...
0
votes
1answer
452 views

sqlite database table with 1000's of columns

I am using sqlite3 in an application to conduct an alalysis of distances between sets of points. Distances between these points have been precomputed using dijkstra's algorithim and left me with a ...
0
votes
0answers
27 views

Designing database without knowing the structure

I am looking for a bit of advice about designing a database when I am not sure what data is required. I am using mySQL. To provide a little context, I am building a reporting system. So to start ...
0
votes
0answers
43 views

Assistance with appropriate schema for this dataset involving the services of parcel couriers

I would like some assistance with the appropriate schema for this dataset. I initially thought that it would be simple to determine which table should contain which data and which columns should ...
1
vote
0answers
25 views

Database design for a customer and payment system

I need help with database schema design. Business rules We have users, each user can have listings. Each listing has one or more license. There are n type of licenses (products) Each listing can ...
0
votes
1answer
12 views

Is there such a thing as an OLAP cube that can change the number of levels in a hierarchy without having to be rebuilt?

The current analytics solution that my team works with cannot handle changes in the structure of one of its hierarchies. If a new level or branch is added, then the entire domain must be first backed ...
2
votes
1answer
1k views

Apparantly errorneous Exceeds 8K row length message

In attempting to alter an SQL Server table adding DATE and FLOAT columns. I get a message: Warning: The table "CustomerTransactions" has been created, but its maximum row size exceeds the allowed ...
1
vote
1answer
216 views

Modelling a database for products, categories and features

Good day everyone. I am building a database for an application and I have thought of two different approaches for achieving the same result, and I would like your opinion. Is basically the classical ...
0
votes
0answers
22 views

Need help on designing database schema for high frequency update app

This app will call API endpoints every second and get JSON data and it needs to be saved in DB and then query it to the client. But Before saving, it needs to check if the data already exists or not, ...
1
vote
1answer
22 views

Help defining a chained one-to-many relationship tables

Sorry if the title is confusing but I couldn't think of a way to properly word this question without laying out what I am trying to do. Basically I have 3 tables and I'm trying to figure out how to ...
-2
votes
1answer
26 views

Requesting review of proposed database for Project Billing [closed]

I've had enough experience with databases to get me this far, but I was hoping someone could validate my design. Once I have this confirmed, next steps would be to implement in some tool, and use ...
0
votes
1answer
86 views

Is it good to save Keywords stored by users in a new DB schema?

My question is more from the schema design and of further performance of the DB. I'm designing a DB schema which will store primarily strings. Actually, users work on these string, as normal mortals, ...
0
votes
1answer
143 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 ...
1
vote
3answers
831 views

How to design parent, child, photos relationships in mysql when photos can belong to either parent or child?

Let's say I have a parent table and a child table with parent_is as foreign key. Now I want to store photos that could either belong to parent or child. so I could have a parent_id and a child_id ...
2
votes
1answer
45 views

Database Design for a Graph (Network Topology)

Is there a recommended pattern for modeling a graph in a relational database? My presumption is that you'd have the following three tables: Graphs ------ GraphID, PK GraphName Each record in the ...
0
votes
1answer
29 views

Schema and Query design for table with multiple languages [closed]

I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and ...
8
votes
3answers
5k views

Fact table foreign keys null?

I am new to data mart design and need to clear a few concepts. I have read up a bit on dimension modelling where I see that fact tables store foreign key references to dimension tables. Now suppose ...
0
votes
0answers
16 views

How would I model a database that selects from a group of tables? [duplicate]

this is the first time I have ever tried to design my own database, so I'm sorry if this is a bit of a stupid question. I have been trying to figure out how to create a database to sort an inventory ...
0
votes
0answers
34 views

Is My Normalization Exercise Correct? [closed]

Considering the following case: I came up with the following designs: 0NF: Order [OrderNo, OrderDate, (CustomerNo, CustomerName, CustomerAddress, CustomerShippingAddress, CustomerPhone, ...
1
vote
0answers
267 views

Interesting Database Designs for Product Catalogue including a Recursive Approach

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
694 views

Designing a database structure for a permissions system

Scenario I would now like to implement a permission system so that a user is able to restrict access on his contact properties to his connections, e.g.: A wants to allow B to access his name, ...
0
votes
1answer
35 views

Groups and Members database: Which of these two options is a good design? [closed]

Each group can have either 1 or 2 members, no more, no less. Every select of group data will need to retrieve the member data. The eventual state of each group will tend towards 2 members (full group)....
0
votes
2answers
20 views

Database layout to keep track of the “freshness” of a column value in a specific row

I have a Person with columns like name, address and bio. How do I store data in order to know the "freshness" of these columns? For instance, "for the Person Y, the name has changed 2 days ago, and ...
1
vote
1answer
40 views

What do the entity-relationship diagram (ERD) symbols used in DBeaver mean?

I don't understand the ERDs used by DBeaver. The diamonds and dots at the ends of the line arent the crow's foot I'm used to. I've googled the subject and checked the docs but can't find a straight-...
0
votes
1answer
31 views

What is the advantage of setting a SSN column as VARCHAR(32) over VARCHAR(255)

What is the advantage of setting SSN column VARCHAR(32) over VARCHAR(255) even though the two of them consume 2-byte memory?
1
vote
1answer
29 views

Duplicate column or foreign key lookup?

I have a base user profile table that stores data for users. Each user is able to upload multiple pictures so this is represented by a separate photo table. When the user profile is queried, the ...
0
votes
0answers
43 views

Is a Booking System where User and Participant are Different Entities Correct?

Background I am building a event booking system for a youth organisation. The events that can be booked are targeted in general at children under the age of 18 (i.e. which in Europe means that you ...
1
vote
3answers
5k views

Adding Row Version to SQL Server table

I have a table in a database with three columns: ID, Key and Value. Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's ...
3
votes
2answers
1k views

Two ways to define may-have-a (1..0-1) relationship

Today I have discovered for myself that may-have-a, one-to-zero-or-one, (1-0..1) relationship can be implemented in two different ways. Let A be the "main" table (left side of ..) and B be the "...
0
votes
1answer
124 views

create table from another table

I have master categories table like this category_id category_name parent_name parent_id 1 animals & pet supplies null 0 2 ...
0
votes
0answers
22 views

Storing objects in key/value stores - serialized or separate values?

I'm playing around with Go and BadgerDB, which is an embedded disk based (not in-memory like Redis) key/value store. There's just so little information about how to use key/values stores in general. ...
0
votes
1answer
17 views

Should I include another column here to keep track of cost + tax or not?

I am making a two database tables tho add a sales order line item functionality to my application. On the second table, which lets call salesorderlineitems, I have the following schema CREATE TABLE ...
5
votes
2answers
241 views

Is there some drawback to define SPARSE columns?

I have a table which has a lot of NULL values in columns. But some columns don't contain NULLs at all (although nullable). Is there some drawback to declare all of these columns as SPARSE?
0
votes
2answers
122 views

Options for structuring multiple associations to a record

This is 101 level stuff and I promise to read a book later, but in the meantime, given the following three tables: Product --------- ProductId (uniqueidentifier) User --------- UserId (...
-1
votes
0answers
28 views

Are Database Systems like Booking.com's or the one of most airlines at least in 3NF form? [closed]

Background I am designing a new system to book tickets to one of our events. The requirement here is that the event is targeted at children, but the tickets are usually booked by their legal guardian....
4
votes
2answers
75 views

A table with all fields NULLable with important, sensitive data

I have a requirement for a database table to hold sensitive patient information about their lifestyle (alcohol/ drug use / injecting etc). On screen the information is sectioned into tabs - "alcohol",...
0
votes
2answers
82 views

database development environment, MySql

I need to create copy/replication of our databases for the development environment. I'm not sure how to do it. DBs are updating many times during a day; not only new records added but also old ...
28
votes
6answers
5k views

Arbitrarily ordering records in a table

A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
0
votes
0answers
25 views

Database design for a messaging system involving group and private chats

I'm designing a messaging system where users are able to start 1-1 chats with each other as well as participate in group chats. I'm deciding between: Normalizing the underlying chat structure (basic ...
0
votes
0answers
19 views

Designing a generic associative table

I need the ability to associate two entities of the same or different types. Take the concept of planes and trucks that have the ability to radio to each other. +----------------------+ | ...
0
votes
1answer
112 views

Updating inverse side bidirectional association [closed]

I understand (more or less) the concept of unidirectional and bidirectional association in a relationship, but I don't see the correct practical application of it. For example, I want an contact ...
2
votes
1answer
326 views

Creating a Friendships database that includes a Block Friend option

My database teacher asked us to program a clone of Facebook using MySQL as the database management system, so I'm trying to represent a friends relationship in the corresponding database. So far I ...
6
votes
1answer
125 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 ...
44
votes
9answers
5k views

How should deletions be handled in the database?

I'd like to implement an "undelete" feature in a web application such that a user can change her mind and recover a deleted record. Thoughts on how to implement this? Some options I've considered are ...
2
votes
1answer
149 views

Lossless Join Decomposition Criteria

On Wikipedia, it says: The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in F+ (where F+ stands for the closure for every ...
0
votes
1answer
87 views

Create one-to-many to two fields in different table

I have a database where I am trying to figure out how to join a table that contains an object called ModelType. The calibration table contains two fields (EffModel and BkgModel) that are based on a ...
2
votes
1answer
84 views

Database Design when Admin and Employee have similar roles except Admin can see all the other Employees Data

We are building a system with concept of Admin and Employee. so basically Admin is an employee with all powers and can view all the data created by other Employee. CREATE TABLE `Vendor` ( `...