Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
6,289
questions
2
votes
0
answers
31
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 ...
- 151
2
votes
0
answers
72
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
2
answers
147
views
How do I guarantee inventory numbers are unique across all tables?
I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords ...
- 254
2
votes
1
answer
3k
views
Convert OLTP database to OLAP Datawarehouse
(https://i.stack.imgur.com/r5Qjm.jpg). Basically I need help to convert that OLTP Model to Data Warehouse in order to be used for further OLAP Cubes. I understand well the difference between the Star ...
2
votes
0
answers
729
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 ...
- 121
2
votes
1
answer
786
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 ...
- 157
2
votes
0
answers
81
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}
...
- 153
2
votes
1
answer
246
views
Ranking items on a per user basis
I have a MySQL DB with 20k-50k items and I want various users (let's say 1k-10k) to rank these items for themselves, so this is not going to be a global ranking but a per user one.
All items are ...
- 123
2
votes
0
answers
959
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,...
- 21
2
votes
1
answer
3k
views
Database design for ordering complex products
I'm working on a platform in which you can order food from different sellers. Currently the platform is working and restaurants can create food products while clients can order them.
Easy enough.
A ...
2
votes
1
answer
299
views
Best Practice : Schema design for Large Number of rows with few columns [closed]
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 ...
- 121
2
votes
0
answers
191
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 ...
- 155
2
votes
0
answers
105
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 ...
- 143
2
votes
1
answer
411
views
Design a many-to-many table for scale [closed]
I am thinking about scaling issues with many-to-many join tables.
I have a constructed example as following:
Many users have many wishlists.
On every wishlist are many items.
If I scale this table ...
- 29
2
votes
0
answers
2k
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 ...
- 121
2
votes
0
answers
168
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. ...
- 21
2
votes
2
answers
291
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 ...
- 353
2
votes
0
answers
76
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 ...
- 173
2
votes
0
answers
233
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 ...
- 667
2
votes
0
answers
534
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 ...
- 171
2
votes
0
answers
87
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 "...
- 121
2
votes
0
answers
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) ...
- 121
2
votes
0
answers
72
views
Better performance to have nullable columns or an extra table? [duplicate]
There are many ways to get rid of nulls in tables, and I have heard of various reasons to get rid of them, but can anyone show me some real numbers as to how your database might perform differently ...
- 121
2
votes
0
answers
432
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)....
- 803
2
votes
0
answers
59
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 ...
- 21
2
votes
0
answers
918
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 ...
- 129
2
votes
0
answers
337
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 ...
- 138
2
votes
1
answer
2k
views
Building a restaurant database
I am designing an Italian restaurant database, but I face some problems considering the database design, which lead me to a halt. The part that I find difficult developing is the dishes table. A main ...
- 23
2
votes
1
answer
2k
views
Best practice for when the ideal number of columns is unknown
I'm currently making a MySQL database to list products for an e-commerce site. Each product will have several sizes available which will each have their own dimensions and measurements (e.g. length, ...
- 123
2
votes
0
answers
865
views
Database Design: Include Question Answer and Comment tables into Post table
I'm designing a database. This system has question, answer and comment.
Question has some type of questions. In each type, there some differences so they're have some difference fields.
Answer and ...
- 199
2
votes
1
answer
101
views
should I change mysql platform for higher innodb_buffer_pool_size?
Currently Im running a 160G MySQL database on Xampp (Windows). But Xampp has upper limit for innodb buffer pool size of 4G. My computer has 16G RAM. So if I want to make full use of resources, should ...
- 21
2
votes
0
answers
400
views
Improving performance of a weighted search query in SQLite3
For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight:
CREATE TABLE search_index (
id INTEGER(7) REFERENCES other_table,
...
- 121
2
votes
0
answers
4k
views
Storing table history - is this a good use case for Postgres's table partitioning / inheritance?
I currently have a products table which stores product information (title, description, tags, manufacturer, etc.) for a website. I would like to store historical information of changes to this table -...
- 183
2
votes
1
answer
206
views
What should we do when same column fields occurring multiple time in many tables?
I have more than 20 tables in my database. There are some columns which occur in almost all of the tables. In that case what should I do? Do I put all those columns in every table or is there a better ...
- 123
2
votes
2
answers
83
views
Advantage of exposing data model to customers using our product
We have a product through which we provide a standard definition to customer's asset data. The problem currently lies in importing customer's data from different types of data sources to our data ...
- 21
2
votes
0
answers
419
views
Is the table in 3NF?
I am creating a relational database - a library management system. I want to show an example where I normalize the data from 2NF to 3NF. My example of 2NF looks like
The definition of third normal ...
- 63
2
votes
0
answers
67
views
Filegroup design for Document Table for BLOB Storage
I have a table for storing documents as VARBINARY types in my database (I'm not using filestream). My database is setup with only one data file, but I am wondering if there is a benefit to creating a ...
- 19
2
votes
0
answers
23
views
Counting daily distinct records [duplicate]
I have a table that looks like this in Google Big Query:
custID InteractionDate Purchased? Sales
1 20150312 F 0
1 20150312 T 200
1 ...
- 133
2
votes
0
answers
2k
views
Finding missing_index_details in MySQL
MS SQL Server apparently can tell you what indexes would be useful for your query. Is there anything similar in MySQL?
This article (scroll to "Find missing indexes") says that in Microsoft SQL ...
2
votes
0
answers
141
views
How to query against legacy data that needs to be cleaned up?
I have an existing SQL Server DB with 3 tables (note: this is a made up example, but modelled after a real problem).
Artists : contains music artists
MusicRecords : contains information on music ...
- 121
2
votes
0
answers
5k
views
Multiple user roles with different attributes
I need to design a database that has multiple users, with multiple roles. For instance, a user can be an admin, company, employee, or customer. Each user can have any number of these roles. Right now ...
- 121
2
votes
0
answers
316
views
Covering Index - Insert Performance
We are running some performance tuning on our database in SQL Server. The database in itself is pretty small with around 13 tables. But there are multi-level relationships between tables.
When I try ...
2
votes
1
answer
641
views
Duplicate table structure with different data [closed]
I am looking for a way to resolve a design issue with a Microsoft Access project that is being converted to use SQL Server 2014 as a backend.
We have several Access backend files, two of them have ...
- 141
2
votes
2
answers
1k
views
Optimize large table design
First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a ...
- 37
2
votes
2
answers
201
views
Should running number be separated column from primary key in database?
I have questions that blogged my mind on separating member number and member id.
Suppose that we have a database with member id primary key of Guid type. Then, normally, when we view the profile (on ...
- 143
2
votes
1
answer
2k
views
database design for simple comment
I have a following given scenario.
A user can have zero or more item.
A user can comment on the item. (Just simple comment listing with name of people who comment, no need hierarchical structure for ...
- 143
2
votes
1
answer
123
views
MySQL column type to boost performance
I'll have a table where:
userid (link to user's id in users table)
foldername (a varchar, max. 260 char)
entry (varchar, max. 1024)
data (varchar, max. 20kb)
Single user will have several foldername ...
- 23
2
votes
0
answers
111
views
With SSD's and in memory tables, does a snowflake schema become preferred over a star?
With SSD's being very fast (reducing the overhead of joins), while more expensive (increasing the cost of redundancy due to de-normalization), along-with a focus on in memory tables (again reducing ...
- 563
2
votes
2
answers
2k
views
Best practice design when expecting large data in a table
I have come across a way that kind of works for me, and was just wondering if it would have any side effects, or, if I'm just thinking about this the wrong way.
I first had this idea when I was ...
2
votes
2
answers
431
views
Database setup for datalogging [closed]
We're currently starting to build a version 2 of our system and one of the things we've noticed with version 1 is that we'll have quite a bit of data so we started to rethink our approach to how to ...
- 182