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
0answers
8 views

What is Query Containment?

Any one knows what is query containment and where do we use it please? As far as I can search on google, I only found one here. Although, I've got some idea on the subject but i'm finding it ...
0
votes
1answer
23 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
0answers
13 views

PayPal-like database schema design

I am learning how to do accounting and auditing data effectively in SQL which makes wonder how to PayPal would store transactions data in MYSQL in simplest way as possible. So here are the tables of ...
4
votes
2answers
8k views

Modeling invoices and orders

I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless. Model ...
18
votes
2answers
10k views

Ready-to-Use Database models example

Where can I find ready-to-use database models ? I don't need a database with data in it, but only schemas (UML diagrams). Perhaps something like the data models at this link, but much more complex ...
0
votes
1answer
19 views

Table design - parent with always exactly 1 or 2 children

Lets say I have the following existing tables (both with a primary key column named Id): TableA TableB Now, I need to create a new table that groups together 1 record from TableA with either 1 or 2 ...
0
votes
1answer
62 views

Is there a database table design to avoid storing table names in a table?

So my scenario demands to store details of businesses which offer varied services. One business can offer n different services. Since each service is different, details pertaining to a service are in ...
1
vote
1answer
519 views

Error in creating trigger

I have 4 tables: event: event_id(p.k) | uid | circle_id(f.k) activity: uid | performed_activity_id(f.k->event_id) | activity_type_id follow: follower_id | circle_id(f.k) notification: sender_id ...
0
votes
1answer
847 views

How does one create a data base for a survey/questionnaire? [duplicate]

I need to create a database for a wordy questionnaire. At the end I want to be able to graph my information. Where can I do this?
0
votes
2answers
294 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 ...
0
votes
0answers
23 views

Can a decomposition of a database table into 2nf, or 3nf yeild different table results depending on the sequence of splitting?

I am not able to check if the decomposition of a table into 3nf, or 2nf form will depend on the sequence of decomposition. Please help, I want to be assured that even if I adapt different sequence of ...
0
votes
2answers
38 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
24 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
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
453 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
31 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
44 views

Assistance with appropriate schema for this dataset involving the services of parcel couriers [on hold]

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
26 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
13 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
223 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
23 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
144 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
833 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
46 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
271 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
697 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
42 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 (...
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 ...