Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.

learn more… | top users | synonyms (3)

2
votes
1answer
12 views

Have several rows, or one single row with a huge comma separated data?

I have many workers processes working at the same time. They grab an ID from a list called available_ids, work on that ID, and put that ID on a list, called used_ids. The list available_ids can have ...
2
votes
4answers
84 views

Are junction tables a good practice?

Say I have a large table that holds the user's info and another table that holds several locations. Then I use another table that holds the user_id and the location_id. In order to retrieve the data ...
3
votes
1answer
229 views

CouchDB and document versioning

I'm currently working on a wiki-esque application using CouchDB and am trying to implement a document versioning scheme. The way I see it there are two ways of doing this: Store each version as a ...
0
votes
2answers
31 views

Where to place a foreign key column?

This isn't exactly related to modeling the database but rather about "standardizing" column placement. Where should I generally put my FK column? As the last column of the table? At the start just ...
1
vote
1answer
14 views

How to structure different types of a model in MySQL?

I am building an application where users can apply for small business loans. There are 4 types of loans (A,B,C,D). There are 10 commons questions no matter which type they choose, but then based on ...
3
votes
0answers
37 views

Efficiently storing sets of key-value pairs with wildly different keys

I inherited an application that associates many different types of activities with a site. There are roughly 100 different activity types, and each one has differing set of 3-10 fields. However, all ...
1
vote
1answer
33 views

What is the best SQL Server field type to cast to when storing a UNC path stored in a sql_variant field?

What is the best choice of field type to cast to when storing a UNC path in a sql_variant in Microsoft SQL Server 2008? It appears that I could store up to 4000 characters according to Microsoft ...
2
votes
1answer
269 views

Table design problem

I'm looking for a SQL design solution for the following problem. The idea is that I have a validation form that contains the client's data. Each client can log in to his account and edit his data. ...
0
votes
0answers
10 views

Data Warehouse for reporting - 6 Dimension tables,or 1 table and 6 views?

Problem: I have been asked to add a Dimension table to our data warehouse and create the ETL tasks to populate it. Lets call the table "DimInternalFault". The source table for DimInternalFault is ...
0
votes
3answers
513 views

Rent weekly cost database design

I have a database which contains a table BUILDING with in each row details about some building, another table BUILDING_UNIT contains rows with details about a single building unit which refers with a ...
1
vote
1answer
124 views

Reviewing relational database tables

I am building a database, but I am a little bit confused with my table relationships. I wish someone could help review my table relationships below for me and tell me where I am going wrong. I have ...
4
votes
3answers
295 views

Help with a scenario of music groups and members in ERD design

Can someone please show me how to construct this ER diagram with the following scenario? I’m confused with the ‘or’ part of it. Description An Artist has a Name, and must be either a Group or a ...
0
votes
1answer
136 views

Enhanced ER Model Supertype / Subtype modeling issue with inheritance and recursion

Before posting, I have looked for a good solution on internet and I have coined this post for 3 days in row and trying to think through it prior this post. So if I have missed something or got it ...
4
votes
1answer
28 views

Snooping-safe Vote tables

I have a table with both user_id and vote. They are stored together so each user can only give one vote. However it's also problematic because admins can simply read who voted what. Is there a way to ...
4
votes
0answers
33 views

Is this a sane model for a network provisioning system?

I'm sketching out a model for a network setup, where I have the following conditions: 1. There are multiple nodes which contains a number of switches each. 2. Each switch has multiple services, which ...
3
votes
1answer
22 views

Single table or multiple table design when records have a clear distinction but are queried together?

Let's say I have the following types of recurring data (all types periodically come in). Post (Title, Summary, Text, Timestamp) Image (URL, Caption, Timestamp) Event (StartDate, EndDate, Title, ...
2
votes
1answer
113 views

Recipe Health risk Database ERD structure

I have these tables: First table(Recipe table), Recipe recipeid(PK,AI) recipename ingredients Second table(Ingredients table), Ingredients ingredientid(PK,AI) ...
3
votes
1answer
32 views

Modeling discounts - Product specific and Customer specific discounts

I have a reasonably simple database but have the following problem: I'm modeling a store + a lot of non-relevant stuff database. I have the following problem. I want to allow for specific products to ...
2
votes
2answers
45 views

Modeling invoices and orders

I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless. Model ...
2
votes
1answer
20 views

Database design, many rows or many columns?

I am aware that this has been discussed before, but I've read conflicting information about this particular topic, so I'm hoping for a bit more decisive opinions from you. I have multiple tables, ...
4
votes
2answers
145 views

Conceptual ERD Multi-table many to many, or possibly recursive?

I'm creating a conceptual diagram [yes, I know I've included attributes and keys - but this is just for me to consolidate what I'm doing whilst learning] -- so please treat it as Conceptual with the ...
1
vote
0answers
12 views

Party Data Model / Universal Data Model Example

I am trying to implement Party Data Model / Universal Data Model in our application which needs to store different relationships between entities (People, Companies). Example of relationships: ...
2
votes
1answer
39 views

Database Table Design

I would like to ask couple of questions regarding my schema table design. I have the following data coming from an external database where I do not have any control over it. PROD_NO PROD_NAME ...
-1
votes
0answers
19 views

A good DB Design

I want to design database where possible types of questions are Primary Question Set --> Secondary Question --> Question Primary Question Set --> Question Question. I short some question have ...
1
vote
0answers
36 views

Basic database relations [migrated]

I recently opened a travel agency and I need a database but don't have the money yet to have it done by a professional... A very basic thing will do until I can have it done better. We do ...
3
votes
4answers
9k views

What's better/faster? MySql or FileSystem?

Let's imagine a web site that is a directory of people. For each person there may be a profile photo and a biography. I'll admit my SQL queries could be better but in general what would be faster and ...
0
votes
2answers
19 views

How to decide when is use ERD or UML for data modeling?

A UML diagram can be used both to model database designs and class designs, but an ER diagram should be used exclusively in database problems. How do you pick one graphical representation over the ...
1
vote
0answers
18 views

Uninitialized values: ignore or set?

From the implementation point of view for a database, is it preferable to handle optional form fields by setting to some default value (e.g. false, 0, "", etc.) or just ignore such fields (do nothing ...
2
votes
0answers
42 views

How to model an activity feed

I'm having a hard time modeling an activity feed that should show events based on user roles. I'm using Postgresql 9.4. I have a projects table. Each project has many users through a memberships ...
1
vote
1answer
491 views

Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating. The database I'm using is postgres. That's not really something I can or want to change (unless there's very good ...
1
vote
0answers
47 views

Database design/layout

I have an objective to record and store test data. A secondary objective is to track the progress of the test programme (it is a large programme). So I want to use MySQL (not Access or DOORS) to ...
3
votes
1answer
58 views

Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)?

I am trying to improve the data storage in biomedical science lab where I recently started working. The existing workflow is atrocious, involving many differently formatted Excel sheets that all get ...
4
votes
1answer
127 views

Is it ever ok to not test a feature? [closed]

Is there any point at which you become so familiar with your language/database/system that there is no need to test a new feature/configuration/query/etc. by contained/simulated testing before ...
2
votes
1answer
49 views

DB Schema design : single table with more columns vs multiple tables with fewer colomns

What would be better DB design for a social network website. A single table with more columns and less rows or multiple table with fewer columns but more rows. For example: User can post an update on ...
1
vote
3answers
76 views

Multi-table optimal database design

I'm a bit new to the database design. Thus this question to people who may have more experience. I need to design a database that needs to store statistical data for many systems. The data is ...
2
votes
1answer
32 views

table structure for events

Got a bit stuck, need a hand. I need to store events at the database, and events may happen at different location multiple locations at the same time so these are my tables CREATE TABLE ...
0
votes
1answer
204 views

Allow access to enter data through forms, but not full database access

Using Access 2013 or Sharepoint 2013 is it possible to give someone access to data entry forms that work with a back end database whether Access database or Sharepoint SQL. But from everything I've ...
1
vote
1answer
181 views

Relation between multiple hierarchical taxonomies linked to a single entity

I am working on an online test application which supports multiple choice questions. I have created schema for taxonomy (classification) system to be used with questions. taxonomy(id, name, slug, ...
8
votes
2answers
199 views

Migrating from text and image to varchar(max) and varbinary(max)

I have a SQL Server database which contains a number of image and text columns, and I'm studying potential problems that might arise from migrating them to their non-deprecated counterparts ...
0
votes
1answer
173 views

Need Advice on Normalizing Database

I have a group of database tables that lack normalization. They all have a similar primary key called 'doc_index', which is either a PK by itself or part a composite PK. As you can see below, there ...
0
votes
2answers
33 views

Performance impact of adding a foreign key to a 1M rows table

I have an ecommerce database running MariaDB + InnoDB, which has a table to store the transactions (T) and another which stores quotes (Q). I'd like to make a connection between T and Q by adding a ...
0
votes
3answers
91 views

Is it better to use an ID (int) or a Name (varchar) as PK for a table? [closed]

Let's suppose I have an ORDERS_LINE table and a PRODUCT table and the ORDERS_LINE table must have the information of which product was ordered. I prefer to use a Primary Key based on the Name since it ...
1
vote
2answers
177 views

Working NoSQL scheme for a betting app

I'm building an app where a user can bet on the result of a sports game. Basically, we feed the database with competitions and games, and the users can place their bet in the form of a score ...
0
votes
1answer
192 views

Duplicate Relationships in Rails/MySQL Healthcare Application

Overview of the Application I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and ...
0
votes
0answers
19 views

How to recover corrupted dbf file? [closed]

I have a VFP form that has the sct file corrupt. how can i solve this problem? I used foxpro in built software
3
votes
1answer
289 views

Building a branched versioning model for relational databases

I am database designer and at my current project I'm implementing versioning capabilities required to concurrently edit rows of data in RDBMS. The project requirements says, that data editing sessions ...
1
vote
1answer
147 views

How to model when you could have many (thousands of) attributes to a table (product)?

We have a main table, let's say, Product. It has several general attributes like 'Product Name', Manufacturer ID, etc. As of now, all these attributes are implemented in the data model as columns to ...
1
vote
2answers
125 views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's ...
0
votes
1answer
49 views

Can I somehow Improve the relationships between tables in the following sample?

I am currently developing a database for a school management system... There are tables for GRADE, SECTION and SHIFT... GRADE ==== id PRIMARY KEY SECTION ==== id PRIMARY KEY SHIFT ==== id PRIMARY ...
2
votes
1answer
264 views

Design advice regarding a users/groups/tasks database scheme

Here are my requirements: one user can have many tasks one group can have many task one group can have many users only invited user by a user can be added into a group Does this db design appear ...