Questions tagged [database-design]

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

Filter by
Sorted by
Tagged with
5
votes
1answer
1k views

Designing a database structure for companies and stock owners

I'm working on a personal project where I want to make a database that holds the information for every owner of every public company. Say for example "Sears INC" and the program would get the ...
4
votes
6answers
1k views

Database design for an exam

I am designing database for an exam, and I am stuck. I do not know how to do it. Here is the relevant information: Student answers 10 questions. Each question has 3 options (answers) and student ...
4
votes
3answers
1k views

Best way to model a single user with multiple usernames

I'm looking to write a website where a user will login with their email address and password, but can have multiple usernames. The context is a social gaming website. This may result in a user ...
3
votes
2answers
409 views

Should I create multiple tables for different task states?

I have a tasks table in my database and, in the business domain of interest, tasks can have multiple states: open, begun, in review, completed. Despite having open and begun in the same table, ...
3
votes
1answer
2k views

How to normalize my db schema for a quiz application? [duplicate]

I am putting together a quiz application. (I'm calling each quiz a "challenge") I have a "challenge" table and a "challenge_question" table which is pretty straightforward. challenge table: id ...
2
votes
1answer
1k views

Multi-tenant database

We are building a database for a SaaS web application. We have been told to use a multi-tenant approach and to expect over 100,000 tenants to be using the system over the next 5 years, so we're ...
2
votes
1answer
477 views

One to at most one relation or multiple null columns?

I have a table with many attributes that can be grouped in logical groups, and thus the idea of put this attributes in separate tables seems attractive. The logical groups doesn't represent entities ...
2
votes
3answers
388 views

SQL How to Convert Single Tenant Databases to a Multitenant Database

We have a legacy application. For some reason, someone decided to create a database for each customer. So we have 500+ databases with the same schema. I would like to convert into One multi-client ...
2
votes
1answer
972 views

Data Model for Online Examination - Matching Questions

I'm designing a module for a client that allows them to administer certification examinations via their website to seminar attendees. I'm stuck on how to store the data for matching questions and ...
1
vote
1answer
979 views

Pros and Cons of having additional columns in join table of many to many relationship

I searched but did not find any discussions on this topic. Are there any pros and cons on creating many extra columns on joined table? Does it affect speed of data retrieval because we are joining 3 ...
1
vote
3answers
706 views

Heterogeneous ordered list of value

I have the following object model: class A { List<Thing> orderedListOfThings; } interface Thing { } class X implements Thing { int attr; } class Y implements Thing { String ...
1
vote
1answer
12k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
0
votes
1answer
4k views

Db design for a quiz with variable number of answer choices, and each worth different score in a different category?

I'm working on a question/answer project and having a hard time coming up with a suitable database structure. Each question can have from 1 - "n" answers, and each answer is associated with one of ...
11
votes
2answers
2k views

Database for efficient range aggregate queries?

As a simplified example, suppose I have a table like this: seq | value ----+------ 102 | 11954 211 | 43292 278 | 19222 499 | 3843 The table may contain hundreds of millions of records, and I need ...
7
votes
1answer
5k views

What's difference between standard view and indexed view in SQL Server [closed]

I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't? Indexed view needs to be ...
5
votes
2answers
942 views

Maintaining referential integrity in a booking system

I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005. I have 3 tables (simplified for this question). Table 1- CourseSize table- this determines the ...
5
votes
1answer
6k views

Two-column foreign key constraint only when third column is NOT NULL

Given the following tables: CREATE TABLE verified_name ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, UNIQUE (name, email) ); CREATE ...
5
votes
2answers
2k views

Constraint many to many table between two child tables

New to this exchange site so I hope my question isn't inappropriate. Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently. One table has a One-To-...
5
votes
2answers
921 views

Best fill factor for GUID clustering key

We have a database that uses GUIDs for most of their columns in a data (non sequential). Does anyone have any best recommendations for fill factors on a GUID database in an hgih insert/update database?...
4
votes
1answer
164 views

Checking existence and syncing data with millions of rows

I am developing sms based service platform. In India, we have two different types of routes to send sms - promo & trans. Sms sent via promo route has to be checked against nationally maintained ...
4
votes
2answers
2k views

Save history editable data RDBMS

I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
4
votes
1answer
458 views

Proper Database Partitioning

I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are ...
3
votes
3answers
312 views

Understanding data abstraction levels

The three-level ANSI SPARC Database Architecture suggests three data abstraction levels, namely, external, conceptual, and internal levels. If I understand correctly, the external level represents ...
3
votes
1answer
6k views

Is it “OK” to have a table that only contain foreign keys?

In "OK", I mean "it doesn't have any bad impact with the overall database design". For example, will it cause insertion problems? or will it be a problem in the future when I add field in the table?...
3
votes
2answers
5k views

Best design for a tree-like structure database

I'm designing a database in MS Access which is used for tracking the locations of a variety of chemical solutions, made in batches. Each batch can be split (aliquoted) practically indefinitely. That ...
2
votes
2answers
1k 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 no ...
2
votes
2answers
1k views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
2
votes
1answer
649 views

Multiple values in lookup fields

I know newer versions of MS Access allow multiple selections in lookup fields. For example, if I'm collecting information on my students, I can have a field called Race with lookup values that ...
2
votes
1answer
2k views

Restrict two specific column values from existing at the same time

I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed. I would appreciate any help creating a constraint that will enforce this. CREATE TABLE example ( ...
2
votes
1answer
248 views

MS Access + SQL Server for 1160 users - Is it worth it? (Please Help!) [closed]

Our school in Delhi (India) wants to develop a professional and robust in-house database (SIS) that should handle vast amount of student information, including admissions, registration, re-enrollment, ...
1
vote
2answers
165 views

Is this db/table structure sensible?

I'm not very experienced in database design so bear with me. I've taken over a ruby/rails app. with a connected MySQL/innodb database. As I'm updating the server to the latest ruby/rails version I ...
1
vote
3answers
2k views

Remove duplicate values from query result

I am using Postgres 9.3.9. I have 2 tables : f_agent : CREATE TABLE f_agent ( f_agent__id bigserial NOT NULL, f_agent__total_users integer NOT NULL, f_agent__dim_time_id integer NOT NULL, ......
1
vote
3answers
150 views

Make column default a query

Is there a way I can set up a column in PostgreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...
1
vote
3answers
133 views

Relational tables with same content, custom solution

So let`s say that I am building an app where I can add comments for FOO. Normally I would have something like this: Table foo and table foo_comments, where foo_comments contains id, foo_id and other ...
1
vote
5answers
509 views

Should I have a column for every data type or 1 column that stores everything

I have a database table that currently looks like this: PageField -Id (Int, PK) -FieldType (String) Can be Text, Decimal, Integer or Bit -Value (This stores the value regardless of the FieldType, so ...
1
vote
3answers
324 views

designing a database with type and sub type

I am trying to build a publications database, and i want to develop a genre relation that has various (maybe look up tables) sub-genres attached. as an example, in the genre relation there would be a ...
1
vote
1answer
911 views

table design to track significant events associated with an item

An Orders table I'm dealing with at present in design phase has about 10 or more columns. Each OrderID in the Orders table will have certain significant events or milestones. The number of Orders ...
1
vote
1answer
3k views

Database design for dentists

I have a homework, where I should create a database for a dental clinic using MySQL Workbench. This database will be used later for a web app for dentists. Here is a picture of the database design: ...
1
vote
1answer
2k views

Parent/Child table design?

Lets say I have a table to hold Tasks and then I have a table to hold Sub Tasks, is it better to break Tasks and Sub Tasks into separate tables or keep them both in the same table with a possible ...
1
vote
2answers
249 views

Database of the website “eMovies”

Here is my exercise: Given database of the website "eMovies" that manage data about cinema movies. build the given database in Access and create the relationships between the tables. My attempt: ...
0
votes
1answer
133 views

Comma List vs Multiple Records

I am currently building out an application that will contain many users, two roles and two groups. Logically we want users to be able to be administrators of other users within the two groups. User ...
0
votes
0answers
103 views

Multi-Tiered Database Design Issue

I am trying to build the multi-level model as demonstrated. When a user creates a campaign they will select some categories and some metros from tables Category and Metro. Those selections will then ...
0
votes
2answers
6k views

How can I better optimize my tables and a LEFT JOIN query to find items that don't yet exist in the right table?

I have two tables that represent a list of urls and their related word indexes. Here are the table definitions for reference. desc urllist; +-------+---------------------+------+-----+---------+------...
0
votes
2answers
108 views

Modelling users and groups - but with multiple types of groups

I'm modelling a user management system in PostgreSQL that is fairly typical in that it consists of users, groups and permissions (and the relationships between them). However, its seems I'm dealing ...
45
votes
5answers
46k views

When should you denormalize?

I think we are all familiar with database normalization. My question is: What are some guidelines that you use when you want to denormalize the tables?
41
votes
5answers
36k views

How to design a database for storing a sorted list?

I am looking to store a sorted list inside a database. I want to perform the following operations efficiently. Insert(x) - Insert record x into the table Delete(x) - Delete record x from the table ...
31
votes
5answers
3k views

Is it OK to keep a value which updates in a table?

We are developing a platform for prepaid cards, which basically holds data about cards and their balance, payments, etc. Up until now we had a Card entity which has a collection of Account entity, ...
23
votes
4answers
13k views

Whether or not to create separate tables for different product types?

I'm in the process of designing a database and I'm having second thoughts about my initial design decisions... Product types are as follows... Models, parts, replacement part kits and options. ...
14
votes
1answer
13k views

Understanding a notification system

I have been looking into how to build a notification system on SE and elsewhere and found myself drawn to the solution that is the accepted answer here: https://stackoverflow.com/questions/9735578/...
9
votes
3answers
28k views

Schema design for products with multiple variants/attributes?

I'm using MySQL. The idea is similar to shopify with a different concept, so users are going to add their own products with multiple types of variants and attributes. From all the research I've done ...