Questions tagged [database-design]

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

934 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
0answers
280 views

Is it correct to model a specialized table using a foreign key whose referencing attributes are not also forming its primary key?

In the process of designing a database model, I would like to implement a Class Table Inheritance pattern in a way that does not seem common at first. I would like to know if it is a correct ...
2
votes
1answer
112 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` ( `...
2
votes
1answer
49 views

One status table for all models?

lets say I have a users,posts and categories tables .. users table would have statuses like ( active, expired .. etc ) posts table would have statuses like ( published, draft, archived .. etc ) ...
2
votes
1answer
41 views

Troubles with an attribute that depends on the value of another attribute

I'm redesigning a health insurance costs database (actually they're not really insurance plans in my country but I think that for the sake of the example it doensn't matter). We're the health plan ...
2
votes
0answers
933 views

Double Entry Bookkeeping Database Design - Views vs Derived Tables vs Python Processing

I'm starting to learn a bit about databases, and I've been reading for while about what is the best way to design a database for a simple accounting system with double entry. For those who are not ...
2
votes
1answer
71 views

Create foreign key constraint on indirect data

I am trying to enforce relational integrity across some database design containing sensor data. The relevant parts of the database: CREATE TABLE logger ( id INTEGER PRIMARY KEY ); CREATE TABLE ...
2
votes
1answer
345 views

Drawing an entity-relationship diagram for a store business context

I have an entity called Users, with the attributes Id, Name, and Phone No. The objective is to develop an entity-relationship diagram (ERD for brevity) where Users might be Store Cashiers, and also ...
2
votes
0answers
489 views

How can I draw an entity-relationship diagram for a medical business domain?

I have to develop an entity-relationship diagram (ERD) for a business domain with the following characteristics: A doctor consults from one clinic only, but a clinic may have many doctors working ...
2
votes
0answers
2k views

Database design for many payment types

I'm new to database design. This database is for an upholstery cleaning store. I have some doubts related to model billing part. This store accepts payments using money, credit/debt card, bank ...
2
votes
0answers
134 views

Understanding multivalued dependencies

This is my first time studying multivalued dependencies and there is a question that was posted by my lecturer as a class exercise. I have an answer, but I'm not 100% sure it was right. Please let me ...
2
votes
0answers
630 views

How can I model an ERD with relationships that include “this or that but not both” and “this and that”?

I am having trouble drawing a entity-relationship diagram (ERD) and I wanted to ask for any guidance on what I am doing wrong. Busines rules The descriptions of the assignment and of the business ...
2
votes
0answers
515 views

A Shop can have Products from a Supplier

I'm creating a Platform where Suppliers have Products that Shops can choose, add to their Shop and start selling. My situation is as follows: A Supplier can have many Products (for example: 'T-...
2
votes
0answers
693 views

Modeling a database structure for contact management

I was wondering what was the best design I could make for reusability, long-term maintenance and scaling for my database design. I am currently the only programmer at my job and they need a good ...
2
votes
0answers
126 views

PostgreSQL database re-design considerations

We have a 2TB PostgreSQL database, which contains two groups of tables that account for about 90% of the storage used. These two groups can be loosely described as 2 big tables partitioned, the first ...
2
votes
1answer
331 views

Hashtags and synonyms table design

So what I'm trying to do is setup a tag and synonyms structure. Where you can assign a tag as a synonym to another tag. E.g for tag: photographer I'd want to add 'photography, photographers, photo' ...
2
votes
0answers
375 views

enforcing at least one child in a foreign key constraint (or 1:1..n relationship)

While trying to implement multi-language strings in a postgres DB, I came across "Data Modeling for Multiple Languages (on Vertabelo)". Especially "Approach #4", splitting non-translatable properties ...
2
votes
1answer
181 views

Relationship between two multi-valued child tables

Scenario In my system, there are a number of different TradeBaskets. Each TradeBasket contains a number of different Securities. The Securities have a Type, Value, Sector etc... and new Securities ...
2
votes
0answers
85 views

When to Converge Dimensions in a Data Warehouse When There Are Few Common Attributes

I have two StudentDetails dimension tables in my data warehouse and I want to know whether I should converge them, considering that they have limited attributes in common (keeping in mind Kimball DW ...
2
votes
0answers
322 views

How to resolve “unwanted” recursive relationship?

I'm currently designing a database for concerts, so I have created the following entity-relationship diagram (ERD): Business domain characteristics A concert can take place on a regular event or a ...
2
votes
0answers
122 views

Database Table Schema Circular Reference issue

Trying to fix a Circular Reference in a schema created by a developer, but having a hard time. This is as far as I have gotten. The black line is the tables that are needed to create a file. The red ...
2
votes
1answer
81 views

Approach to save many to many relationship in case select all values

I have Countries mapped to Products as many to many relationships. As a result a new mapping table is created with product_id and country_id. If a Product is saved then multiple country_ids are stored ...
2
votes
1answer
178 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 ...
2
votes
2answers
204 views

How to model a database structure for exam dates and date ranges?

I have one table Exam, and I want to store exam Date. Each Exam belongs to one of the Categories described below: Category 1. For Exam A, there can be a Date once in a year, e.g., 01-01-2017. ...
2
votes
0answers
85 views

Is it good practice to have NULL fields dependent on the value of another field? If not what is the best alternative?

I'm designing a database to hold (biological) experimental data. The database needs to be flexible enough to data from different experiments - so some experiments measure relative levels of protein ...
2
votes
1answer
110 views

Designing an extensible authentication system

I need to design an authentication system, where different type of authentication schemes may exist. At the end, I should assign a passphrase (access token) to the user. I have several authentication ...
2
votes
1answer
356 views

Need Help Designing Tennis Ladder Database

I'm creating a tennis ladder website using PHP/MySQL/CSS & HTML. A tennis ladder is basically a system where you challenge a player and if you beat them you go one spot above them on the ladder ...
2
votes
0answers
381 views

Normalization vs denormalization for these kind of data?

I'm using Firebase realtime database (NoSQL), And trying to figure out the best database design for my data. My data is primarily about discounts, each affiliator introduces a number of discount ...
2
votes
1answer
66 views

What design should I use?

I am creating a program that will monitor several servers and computers. A user can create an account and have multiple computers in his account that will be monitored when the user installs the ...
2
votes
0answers
72 views

How should I select a primary key in my use case

I have an app wherein I want to give offers to certain devices which fall in range of IMEI numbers (I have a separate algo to calculate this range). So I want to create a structure in which I have a ...
2
votes
0answers
97 views

Choosing database for large set sum and difference

I am creating application, with one server node and multiple clients. Each client holds Set of Strings, that will need to be stored in their own local database. Once a day, all clients will send all ...
2
votes
0answers
529 views

What is the difference between (Superclass/Subclass) Total and Partial Specialization?

What is the difference between total and partial specialization of superclass/subclass (or supertype-subtype) structures in enhanced entity-relationship modeling?
2
votes
1answer
53 views

Online text based game design - Am I linking the databases correctly?

I'm trying to recreate an old game that I used to play that went offline quite some time ago. I'm doing this as a hobby project for myself with the hope that maybe a few hundred people might enjoy it ...
2
votes
0answers
172 views

Database design - reusable elements (templates)

Suppose I have a table that stores items of a very specific type. Items of this type are stored in a bank for future use, and they can be changed specifically for a situation without being changed in ...
2
votes
0answers
29 views

Attribute: Majority single value, but multiple on occasion

Here is the situation: There is a dimension with a natural key that is a good candidate for a primary key. However, there are a few columns that can have (and does have - 10% of the time) multiple ...
2
votes
0answers
63 views

Looking for suggestions to model a database of Electrical Components

We have a database of electrical components and I am trying to make sure I have a good design. I am not a database designer (my comfort zone is designing with these hardware components), so I am ...
2
votes
0answers
188 views

Clustered index on a fact table

I'm trying to work out what field(s) to use for the clustered index of my fact tables (which all have a similar structure). I'll use FactSales as an example: CREATE TABLE dbo.FactSales ( ...
2
votes
1answer
167 views

Avoid circular reference with configuration tables

I've got a few tables that define configuration information. This defines what combination are allowed in the system. However, I've got a circular reference on ProjectName which can cause potential ...
2
votes
0answers
370 views

Design university schedule system

I'm not sure if this is the right place to ask for tips and improvements in a database design but I don't know about another one. I want to design a simple University system. Below I'll have included ...
2
votes
0answers
466 views

Class-table inheritance without flag column

I am trying to implement proper Class Table Inheritance (aka Table Per Type Inheritance) in mySQL. AFAIK it's not offered out of the box so some hacking around is needed to make it tick. Current ...
2
votes
0answers
49 views

insert/update from web server or database

I have a data-model on the front-end that I want to insert/update in the database which of the following approaches (or neither!) is considered best practice: # in webserver some_model = {json} ...
2
votes
0answers
487 views

Handling Strong and Weak entity types in a one-to-one relationship

I am modelling a database for a term project and I have come across something I do not have a definite answer to. Diagram I was supplied with the following Entity-Relationship diagram: Description ...
2
votes
0answers
170 views

Should I move repeating foreign keys into separate table?

Idea Users can upload lots of items and send them from processing. From the upload settings I can set a channel to which the uploaded items belong to. Channel is a separate entity. Currently I set ...
2
votes
0answers
100 views

Is this user-storage database system efficient, effective and secure?

I am using: PHP Version 5.2.17-MIT-BACKPORT-GnuTLS SQLite 2.8.17 I have a database called users, which currently has a table called root. There will be others, once I get all the rows for root ...
2
votes
0answers
1k views

What is a good database design approach for my Online Quiz Android Application?

I have been working on an Android Online Quiz Application with PHP & MySQL. I know what attributes i need however when designing the relationship it becomes complex and confusing when I analysed ...
2
votes
0answers
107 views

Creating UNF for multiple actions

I am creating a database design for manufacture company for my coursework. The database design should be able to do these tasks: Buy items from suppliers. (M - N) Items are used for making products. ...
2
votes
2answers
69 views

Mysql - Each mysql database with its own user?

I developed a web app in which each company that registers on the app gets its own database with its own mysql-user credentials. Is this approach fine OR can I use one master mysql-user account to ...
2
votes
0answers
67 views

Any drawbacks to referencing multiple tables from a single column?

I'm working on a system with the following entities: Projects Sections Activities There is one important constraint that needs to be enforced: Activities are always part of one project. Activities ...
2
votes
0answers
221 views

How do I provide a failsafe for retrieval of data from excel to SQL

So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run. I sometimes have ...
2
votes
0answers
330 views

Networking (classmate like) website database model

I am working on a project that consists in building a networking website like the Classmate web site. I have proposed a database model based on the social media database model presented on the ...
2
votes
0answers
73 views

How to implement search for world-wide Wikipedia/OSM places with alternative-names

I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages. Thus, if you search for "...