The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
1
vote
4answers
35 views

“History-based” database design approach?

A few years ago I've stumbled upon a database design approach, but I can't find any information on this for the love of my life. Its main feature was normalizing tables in a way where every "property"...
-2
votes
0answers
16 views

I am developing appointment and scheduling application and cant grab my hand around database design [on hold]

ANY OTHER WAY OF MAKING IT EFFECTIVE! CREATE TABLE IF NOT EXISTS `schedule_master` ( `Schedule_ID` int(10) NOT NULL AUTO_INCREMENT, `Consultant_ID` int(10) NOT NULL, `Type` varchar(30) NOT NULL,...
0
votes
1answer
26 views

How to model multiple types of contacts?

I'm not sure if this can be called a polymorphic association - what is needed is to for each Message to have a Sender Contact and a Recipient Contact. Both Sender Contact and Recipient Contact could ...
0
votes
0answers
27 views

Storing 450,000,000,000 data points in a bitemporal datastore [on hold]

I'm really looking for guidance on an appropriate way to tackle persisting and retrieving 450 billion data points. The details: 15,000 equities 15,000 days of history (approximately 40 years) 2,000 ...
0
votes
1answer
50 views

Restructuring table - start with PK or not?

I am restructuring a bunch of tables in my database (in this case using a smaller data type for some columns - in most cases cutting those by ~50%, and they tend to be large columns). I've gone ...
2
votes
2answers
29 views

Is partial dependency possible In Data Base for Foreign key relationship

I have 3 tables Student, Teacher & User. Student: CREATE TABLE Student( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL); INSERT INTO [dbo].[Student]([id],[name]) VALUES(4,'Ram'),(5,'Raman'...
0
votes
0answers
11 views

Postgresql 10: Ldap authentication failing

I configured the pg_hba.conf of Postgresql 10 with following host Tom Tom XXX.XX.XXX.XX/32 ldap ldapserver=XX.XX.XXX.XX ldap ldapbasedn="ou=People,dc=example,dc=com" ldapbinddn="cn=Tom,ou=People,...
0
votes
1answer
23 views

What does this diamond shape represent in this ER diagram?

And what is testlog in this diagram is it attribute on relationship. Thanks
-1
votes
0answers
17 views

Two query statament, two result set but need combine as Pivot

I have two table which record Monthly-Allowance and OT-Allowance which both table have Department column and Track column I need report that have all department but separate with Track Column 'DL','...
0
votes
0answers
14 views

Database design: repetitive data that needs to be uniquely identified

Imagine a project: I want to build a service containing a comprehensive list of all the books issued in a country (or in the whole world). I have access to various books databases containing imperfect ...
0
votes
1answer
19 views

Need help to design ER diagram

I need to design an ER diagram for a centralized healthcare management system for my college mini-project. Firstly, I came up with the following ER diagram. ER-diagram-1 While designing the tables ...
1
vote
1answer
66 views

Normalization of the this relation just want to know what are the functional dependencies

Consider the Customer account database of the ABC big bank, which keeps data for customers and their orders. A database of only one relation is designed, as shown below: ABC( customerNo, ...
0
votes
0answers
15 views

Microservice - Catalog and inventory tracking on different tables

I'm currently exploring the microservice architecture and base on what I've read so far it's good to separate the catalog microservice/database to inventory and that's where I want to go. The ...
0
votes
1answer
23 views

“Diamond Shaped Relationship” What's the formal term?

Consider the following "Diamond-Shaped" entity relationship diagram: I'm trying to find some formal readings on why this is a bad design - but Googling "Diamond Shaped Table Relationship" only turns ...
0
votes
0answers
20 views

Designing Doctor-Pacient access permissions

Id like to have several admins which in this case are "doctors" which will have access to their patients, however, a patient might have more than one doctor who has access to their data. What is the ...
1
vote
0answers
70 views
+50

indexing reference to multiple tables based on type and cascading them

I have a rather simple "social network" scenario , with Posts (which can include Media, like photos), and likes & comments. Post can have Likes and Comments by different users. Post can have ...
1
vote
1answer
25 views

composite index on bigints

I have a User Relations table, which basically stores all the one-directional relations of users. For example , User A follows user B ; User X blocks User Y User ids: the id of users are bigints. It'...
1
vote
1answer
47 views

Should I not use camelCase in my column names?

I am creating a GraphQL API using Node.js, which foces me to return all the field names in camelCase. In my PostgreSQL database, I currently have all my columns named following a camelCase convention,...
0
votes
1answer
28 views

Functional dependencies (hopefully simple question) [closed]

Consider the simple problem of keeping track of Students: STUDENT( StudentNumber, StudentName, Dorm, RoomType, DormCost, Club, ClubCost, Sibiling, Nickname ) Would ...
0
votes
1answer
33 views

Postgres text column too large for an index - searching for IP address, so I don't think text search will work

I have a Postgres database with a column that has a large amount of text in it. There are potentially multiple IP addresses in the text, as well as a lot of other text. I need to do a query WHERE ...
1
vote
0answers
35 views

Database design - dogmatic view? [closed]

I'm an application developer (full stack, I'm not shy of SQL, actually prefer it to some ORM solutions). The database architect (that has no coding role) for the project I'm working on is adamant ...
2
votes
1answer
32 views

Should i use multiple databases

I have a situation, in which i need to design a database for some bakeries. I will need to design the same thing for many bakeries. For each bakery i will have for example, an Employee table, and a ...
0
votes
0answers
18 views

Strategies/Schema for Unique Constraint depending on Grandparent ID

I'm using Rails 4 with Postgres (newest version). Wondering about design theory and best practice. Here is my current schema: create table Company ( id int not null primary key ) create table ...
0
votes
1answer
45 views

How to optimally structure a table with sparse data

I am working on a project to create a series of tables that are easier to use and more intuitive for fairly naive SQL users. Because of the structure of our data, we are running into a trade-off ...
2
votes
1answer
34 views

Why is this relation not in Boyce-Codd Normal Form (BCNF)?

I have the relation (A,B,C,D,E). The relevant functional dependencies are: { E,B,C } → { D } { A,B } → { C } { E,B,D } → { C } { E,A,B } → { D } The candidate key is (E,A,B). I do not understand ...
1
vote
1answer
46 views

Finding all possible minimal covers

I have a relation schema R = {A, B, C} and the following functional dependencies: A → B A → C B → A B → C C → A C → B How many different minimal covers can I derive from this relation schema? I have ...
0
votes
2answers
55 views

MySQL - Column value depends on values from two different tables

I have a MySQL database that contains these tables (* denotes primary key): TableA: ---------------------------------------------- | *id | time | price | special_price | ---------------------...
0
votes
1answer
23 views

Enforcing type correctness for foreign keys into a generic typed table

We currently have a single table which represents a bunch of equipment (of different types). Since the fields are pretty similar, they're stored in the same table. select * from equipment; id | name ...
1
vote
1answer
81 views

Do I need to update an index include columns every time I add a new column

I work on a team where we have millions of records of data, but not 100s of millions anything crazy. We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is ...
2
votes
2answers
76 views

1-to-exactly-n in SQL

SQL has well understood ways to do one-to-one, one-to-many, and many-to-many relationships. But what about one-to-n, where n is a known natural number that isn't expected to change. (For example, let'...
2
votes
0answers
30 views

Schema for large amount of machine's sensor data variables [duplicate]

I plan to do a database for more machines. Each machine will have multiple sensors mounted that store data like temperature, current, power, fault, manual mode and so on every minute. Each machine ...
7
votes
6answers
2k views

Does an excessive table violate normalization rules?

Here is an example of some tables that exist in a database where I work. The data isn't actually around schools, but the structure is identical. There are four tables: ** School ** School Id, School ...
0
votes
2answers
22 views

Should I create new table, or add new columns to existing one for song lyrics?

I'd like to ask for some advice in whether I should create a new table or just add some new columns to existing table? Honestly I've been thinking about this whole day and I really don't know. So ...
0
votes
2answers
35 views

Reasons against having “concrete” values in own table with only an ID column and the value itself, and composing bigger objects of IDs

This is a follow up question to How to insert values into a highly normalized database without excessive duplicate checking? The current situation: We have an "massive" object that has around 30 ...
2
votes
3answers
91 views

Around 50 % of the tables in a certain SQL Server database have either zero, one or two rows - is this a sign of a good design?

First off, I'm not a database programmer, nor admin or anybody who is responsible for this area, so I might be wrong in many of the following questions/assumptions. I've recently encountered a DB ...
1
vote
0answers
54 views

Schema for social network using the supertype/subtype pattern

Scenario I'm trying to come up with a schema for a kind-of social network app, where users can publish Posts (and photos inside Posts), and also manage a private Diary that has also Posts (not sure ...
0
votes
2answers
33 views

Solutions for Introducing a new Entity In Between An Existing Association

I'm looking for best practices in regards to a specific change in schema. I'm using sql. I had the following entities: Project and batches - a project had many batches. The batches table had a ...
1
vote
0answers
44 views

Creating a time-series database for a power plant scenario

Considerations I need to create timeseries tables in a SQL database, which will hold data about power generated by a power plant. I need four columns there: ( power_plant_id int FK, ...
18
votes
2answers
1k views

Which collation should I use for biblical Hebrew?

Which SQL Server collation should I use for biblical Hebrew? The database under consideration needs to accommodate diacritics (i.e., vowels, accents, trope, etc.).
1
vote
1answer
31 views

Two FOREIGN KEY constraints with SET NULL targeting the same PRIMARY table

I feel like I'm missing something obvious here... I have a situation with a primary table (let's call it People) and a secondary table (let's call it Groups) with two different roles that people ...
0
votes
1answer
20 views

Designing a table with multiple column types and different foreign keys

I have a table item in which different columns are filled depending on the template type of item that it is e.g. |item_id|template_id|brand|model|heading|usp_1|usp_2|icon_1_asset_id|icon_2_asset_id| |...
-1
votes
1answer
32 views

How do I bring a relation/table to first normal form (1NF) when one of the attributes/columns can hold any number of values?

For example, MOVIES (movieid, movietitle) where movietitle can be any number of words, so I believe it's not atomic. How do you bring something like that to 1NF?
0
votes
1answer
30 views

UUID as primary key and download ID

I'm designing a Django Rest Framework API with PostgreSQL. In the database I'll be recording information about documents which can be requested for download. I want the request to include a unique id, ...
1
vote
0answers
52 views

Combine sales data without knowing the number of sold units per one sale

I’d like to consult for a strategy plan of a database structure for the following scenario: My app is for offices buildings sales reports where a building is the basic unit that I need to generate ...
0
votes
2answers
58 views

Table with too many nullable columns - is there a better design?

I have a user table with around 30 columns. Most of these columns are almost always null unless user decides to share additional info about themselves. For example, weight, height, religion, ...
2
votes
1answer
56 views

Database schema for inventory management for multiple people

I am setting up a Postgres database that keeps track of items that entities in my system own. Each incoming datum says that Entity Q has N of Item X. My first instinct is to have a separate table ...
-2
votes
0answers
33 views

Stuck on task with relational database schema about boats of two different types [duplicate]

I just started taking some online courses in both programming and database management, so I'm really a beginner to these subjects. In my study book there's an exercise that I'm stuck on that I'd ...
2
votes
0answers
29 views

Variable layers of data in a multi-country addresses system

My diagram I want to record addresses in my database. The layout I have right now is visible in the diagram below: Considerations Continent, Country, Subdivision and City will be pre-filled, ...
1
vote
1answer
66 views

Is this table really in first normal form (1NF)?

Does this table comply with 1NF, even if CourseNo and StudentNo? Student(StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
0
votes
0answers
24 views

Instant Messaging Schema design advice

I'm trying to build an Instant Messaging functionality in my app as part a bigger project. Chats can have more than 2 participants (group chats) If participant A delete a message, it still should be ...