Questions tagged [database-design]

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

1,119 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
0answers
110 views

How to design normalized relations to store “global/generic” data?

Context I was designing a relational database model to store data about a company and its production facilities. I had a facility relation and many other relations referencing it, most of the time ...
2
votes
0answers
1k views

Best way to schedule structure schema design of a events database

On this case at the events table has a repeated schedule and scheduled by date example case each of event schedule: The event has schedule every month, week and day with start and end of time The ...
2
votes
0answers
523 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
239 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
84 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
45 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
1k 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
1k views

Why do OLAP cubes perform better than Relational databases on certain tasks?

My team uses a Multidimensional OLAP database for a lot of it's analytics and predictive modeling. The standard line in all the documentation is "OLAP cubes are better suited for this type of ...
2
votes
1answer
583 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
646 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
3k 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
161 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
940 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
888 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
1k 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
196 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
440 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
565 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
0answers
98 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
447 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
137 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
0answers
107 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
169 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
0answers
475 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
76 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
1answer
156 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
572 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
62 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
192 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
234 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
0answers
544 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
663 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
52 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
683 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 So,...
2
votes
0answers
188 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
102 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
94 views

Creating relations using tags

I have three tables: Product, Accessory and m:n relation between Product and Accessory. There are currently around 200 products and around 600 accessories. The user wants to have tags on the ...
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
148 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
0answers
72 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
229 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
471 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
80 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 "...
2
votes
0answers
1k views

Design Question - Stock and OnSale Table

Background : I'm designing a solution for a repair toy association. Since they have office - I'll use Access. Plot : Stock will store items - I choose this primary key (Item_Id,Record_date) ...
2
votes
0answers
339 views

Database sharding, denormalization and synchronization

In sharding, it is advisable to follow a share nothing, shard everything approach. Essentially, this translates to: data that are to be used together are to be stored together (i.e. in a single shard)....
2
votes
0answers
54 views

How To Manage Random Data Without Repitition?

We have a database design problem of a Learning System. Please can somebody help us ? These are the requirements : A person can register for a module which has "n" number of questions. The person ...
2
votes
0answers
897 views

How to store data where various subcategories have specific columns?

I'm new to database designing. I'll use MySQL and PHP. I'm designing a database in which I have a lot of categories and subcategories like given below: Product Table p_id p_title category_id ...
2
votes
0answers
296 views

Automatically set table name prefix according to other field in PowerDesigner

I'm working with PowerDesigner 16.5 and I have a PDM generated out of its corresponding LDM. The table names are inherited from the LDM and I'm fine with them, but I would like to generate the DDL ...

1 2
3
4 5
23