Questions tagged [database-design]

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

899 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
0answers
244 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 ...
2
votes
1answer
466 views

Reorganizing a subject area in ERwin

I have a data model in ERwin (r7) that has about 1000 tables in it. I'm right now in the process of splitting it up into various different subject areas to make it a bit more manageable. Here's the ...
2
votes
0answers
654 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 ...
2
votes
1answer
120 views

What is the best design for dynamic assignment of locations for variable sized grid boxes to occupy?

I want to build a database that will have limited single locations to put boxes. It's divided by lanes and bays. Imagine a 4x4 grid, with a total of 16 slots, and every time I query the tables, I ...
2
votes
0answers
275 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, ...
2
votes
0answers
2k 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 -...
2
votes
2answers
56 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 ...
2
votes
0answers
277 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 ...
2
votes
1answer
109 views

Converting a graph database schema to a relational database schema

I currently am using a graph database and am considering moving off of it to a relational database management system like PostgreSQL. My concern is that I have lots of relationships between ...
2
votes
0answers
59 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 ...
2
votes
0answers
1k 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
0answers
116 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 ...
2
votes
0answers
278 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
0answers
102 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 ...
2
votes
0answers
1k views

Identifying functional dependencies

I'm having trouble understanding the logic in use for identifying functional dependancies. Looking at the sample relation below, I understand fd1 - fd3. But when I look at fd4 and fd5, its logic ...
2
votes
0answers
289 views

“Replicate” Table structures with sp_addscriptexec

In our customers environment is a massive central server (running SQL Server Enterprise 2008R2) and some distributed PCs (running SQL Server Standard 2008R2). The central server has a replication for ...
2
votes
0answers
70 views

Constrain referenced rows to two different tables to belong to the same row in another table

Apologies for the potentially confusing question title. I struggled to make it generic! I am designing a wildlife recording database. An observer goes to a site and records the occurences of species ...
2
votes
0answers
352 views

Proper design of routes in Database MySQL

I'am trying to find out the best database design and try to stick with data integrity by using Foreign Keys. But I'm stuck right at the beginning because of limit relations between tables with FK to 1....
2
votes
0answers
2k views

How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?

I have created table as post ( post_id int(15) auto_increment primary key , user_id int(11) , post_text varchar(2000) , post_type int(1), ...
2
votes
0answers
141 views

Should I use SQL Azure to store product descriptions and links to images in Azure Blob

For an html/Css/JQuery public Azure website to be coded using Zurb Foundation html5 responsive framework, I'd like to use SQL Azure (web edition) to store product descriptions and links to images that ...
2
votes
0answers
48 views

A better model for connecting users to different objects, even efter they cease to exist

I've got the privilege to create a small in-house application for managing TODO-lists, although I am not really a developer (I know some PHP and SQL, and got a chance to prove myself). As my ...
2
votes
1answer
74 views

Keys of a relation

I got relation: R = { (ABCDEF) , (A B F -> C, B C -> F, F -> A, F -> B, D E -> E, E -> D)} keys of this relation are: {BCE}, {EF} But I don't get it... why? Why not only {EF}.. it got less ...
2
votes
0answers
639 views

Problem with Database for survey Questionnaires

Bellow I am showing a sample survey question for woman. I need your help to creating a Database. Description: Top part of the survey Questions are Project Code, Name... etc which is understandable. ...
2
votes
0answers
2k views

which role-permission database design?

I want to manage roles and permissions. Most of designs on web look like this tables: Users Roles UserRoles Permissions RolePermissions Here, what is permissions? I am thinking for such a design ...
2
votes
0answers
399 views

Two different ON DELETE paths, how do I need to change the schema?

For a project in school I'm designing a website that sells items via auctions. Think eBay, but on the complexity scale of school project. We went through the process of making an ER Diagram and ...
2
votes
0answers
205 views

Digital asset management

I'm working in a company that currently develops Media Asset Management system and now we want to improve our system for document files. I have to search nearly thousand .doc and .pdf files and index ...
2
votes
0answers
5k views

Schema for an RBAC (Role-based Access Control System)

I want to implement an RBAC, Flat based. I've decided to base my system on the NIST standardization model. Although all fine and well, I have an inclusion to that design. Instead of a hierarchy ...
2
votes
0answers
804 views

How do I auto-fill data in Microsoft Access 2003?

I am creating a Purchase Order Form at work using Microsoft Access 2003 so that it can be saved and archived in a database and reduce paper costs. These are my current dilemmas: I am unable to ...
2
votes
2answers
71 views

How To Add Days And Time For Instructor Schedule

Problem I need to make a database design (ERD) to display a schedule for every instructor, including the following: day of course (e.g. Saturday or Monday) time it will give course (period from 12 ...
2
votes
1answer
2k views

Contact system database design

I'm designing a database for contacts and I'm looking for some feedback on the current design. What I want: A contact can be a person or an organization. A contact can have many groups. A contact ...
1
vote
0answers
20 views

Understanding many-to-many ternary relationship sets

I am new to entity-relationship diagrams. I'm a bit confused when it comes to interpreting ternary relationship sets such as this: Does this signify that every relationship instance of Party will ...
1
vote
0answers
22 views

Aggregation Vs Ternary relationship

I am reading a book about database systems and in the chapter covering database design using ER, the author aims to explain when to use aggregation versus ternary relationship. In short, each Project ...
1
vote
0answers
38 views

Room bookings database - does it make sense to create two tables instead of one column?

There are two types of entities: user and room. A user can book room select room unbook room unselect room "Selected room" is a state that indicates when a user has decided to book a room and so he/...
1
vote
0answers
17 views

verifying non-additive join decomposition

I am reading a DMBA book and have come across non-additive (lossless) join property. The algorithm for decomposition into multiple tables is borderline useless for practical work, so I am frequently ...
1
vote
0answers
15 views

Trouble with inheritance: Separate table implementation for disjoint entities and common ID

I currently have four disjoint entity types (competition, talk, award, publication) that are to be converted to tables. In order to manage certain other data for each, i.e. documents related to each ...
1
vote
0answers
25 views

Mysql Workbench generates an additional FK in child of a child table referencing the Parent of its Parent

I'm trying to create a Part that is a child of Sublot, which in turn is a child of Lot. Mysql Workbench automatically creates the foreign keys, when I insert the many-to-one relations graphically. ...
1
vote
0answers
18 views

Schema design: Anonymous user's data across registration

This is a schema design question; implementation is going to be Mongo, but answers are platform agnostic. Given an app, which has users, sessions, and items. All visitors will have a session ...
1
vote
1answer
43 views

Table design - parent with always exactly 1 or 2 children

Lets say I have the following existing tables (both with a primary key column named Id): TableA TableB Now, I need to create a new table that groups together 1 record from TableA with either 1 or 2 ...
1
vote
0answers
30 views

Database design for a customer and payment system

I need help with database schema design. Business rules We have users, each user can have listings. Each listing has one or more license. There are n type of licenses (products) Each listing can ...
1
vote
0answers
25 views

Centralizing Databases

I have a very challenging scenario at hand and would like to hear suggestions on the solution I am considering implementing. I have several databases that have similar (non-identical) structures and ...
1
vote
0answers
54 views

Aggregation vs Ternary relationship - When to use?

I am reading a book about database systems and in the chapter covering database design using ER, the author aims to explain when to use aggregation versus ternary relationship. In short, each Project ...
1
vote
0answers
47 views

Different “Master” Tables and Same “Child” Tables

I am working on Student Life Cycle management system which is in the design stage. Right now I am creating the database structure. My requirement is as follows: Every year a student faces three term ...
1
vote
0answers
27 views

How to model complex relationship between three Models in Django

I have to model the following entities: BuildingSite, Company, Worker. Each entity has a many-to-many relationship with the other entities, and each relationship some attributes like start_date, ...
1
vote
0answers
75 views

Crow's foot notation--what is the difference between “one” and “one-and-only-one” regarding relationship cardinalities?

I'm trying to figure out the difference between the single-bar and double-bar cardinality indicators as in the figure below: Some sources seem to use one notation while others use the other. I can't ...
1
vote
0answers
50 views

Is a relation with two attributes always in Boyce-Codd Normal Form (BCNF)?

I went through some tutorials on BCNF and got that a relation with two attributes is always in BCNF. My question What if a relation with two attributes has no prime or non-prime attributes? That is,...
1
vote
1answer
40 views

Random selection with a “complex” subset structure

This is my first question in the whole StackExchange world. I'm not sure if this kind of question fit perfectly here or in DBA Meta section so feel free to "flag" or move this question. In my ...
1
vote
0answers
25 views

How to implement correctly from SQL to NoSQL (DynamoDB)?

I am just new in NoSQL and DynamoDB. I try to moving from using AWS RDS to DynamoDB. I have table user which can have more than one of work experiences and can have more than one of interest (table ...
1
vote
0answers
56 views

Is there a name for this type of data structure?

I'm working with a database that has a structure that few of my colleagues have encountered. The database is a fairly standard Oracle SQL database, but it has a number of tables called xxx_param, ...
1
vote
1answer
119 views

Which table gets the FK? Is there a clear Parent-Child relationship here?

I have the following tables: Each ProductLine will encompass many Parts. For this reason, it initially seemed to me that the Part table is a child of the ProductLine table. However, a part is not ...
1
vote
0answers
50 views

UNIQUE constraint encompassing all the columns of a table - is it bad practice?

Is it considered bad practice to have a UNIQUE constraint applying to all columns on a table, essentially enforcing unique rows? My use case is to store tokens from textual message from various ...