Questions tagged [database-design]

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

905 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
1answer
287 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
392 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
116 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
551 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
425 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
727 views

Serialized Inventory Tracking

I'm developing a inventory system with optional serial no tracking (which users can turn on for items they select). in order to keep stock records currently i came up with two options. Option 01 use ...
2
votes
0answers
574 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
114 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
299 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
1answer
153 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
80 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
277 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
113 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
77 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
216 views

Is the following table for a simple music database in 3NF? If not why?

Im new to databases and I'm struggling to get my head around third normal form (3NF). I have been given a csv file where each record has fields song, album, artist and numerous tags. I have put the ...
2
votes
1answer
155 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
173 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
76 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
100 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
314 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
369 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
0answers
62 views

Optimality of Columns vs. Complementary Table in MySQL

I'm starting work on a project where I'll have a table of entries as well as a table of descriptors. The entries will be updated infrequently, if ever, and the descriptors table will be largely ...
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
67 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
505 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
166 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
62 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
176 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
148 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
293 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
425 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
443 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
1answer
195 views

Best Practice : Schema design for Large Number of rows with few columns

I have about a data set that each entry is about 750,000 rows under it with each row having four fields. Each entry is in its' own CSV file currently and I want to get it into a database. Currently I ...
2
votes
0answers
160 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
89 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
67 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
219 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
282 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
70 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
597 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
221 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
51 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 ...