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 (4)

2
votes
1answer
30 views

Distinguishing Records with Related Fields

I'm a newer user (obviously) and would greatly appreciate question writing suggestions as well as answer suggestions: I have a lot of interrelated tables which largely depend on my Products table, ...
1
vote
0answers
14 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 ...
1
vote
1answer
51 views

Sharding VS Replication

To improve query response will it be better to shard the data or replicate existing shards for faster response. In case of sharding the data might be nicely distributed and hence the queries. In ...
1
vote
0answers
34 views

Design of user database and basic authentication process [closed]

I want to know if somebody can give me very briefly information describing the design of a user database and the basic authentication process please? Thank you
1
vote
1answer
93 views

Database schema for entities with two possible owner/parent types?

I'm using PostgreSQL with Sequelize as my ORM. I have one type, User. The second type is Group, which can have any number of users associated with it through a GroupMemberships table. Users can also ...
0
votes
0answers
22 views

SSAS bridge table with Slowly changing dimensions

I am designing a datamart using the basic principles of Kimball for use in SSAS. I have a many many to many attribute relationships (multi-valued dimension objects), however I have a few questions: ...
1
vote
1answer
26 views

Category entity relationship / subtype relationships design

I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to ...
0
votes
0answers
42 views

Versioning database tables

I need to store all the changes made for almost every table in a meta-data database. I am thinking about storing every previous version in a JSON array or XML in the table. Like: CREATE TABLE ...
3
votes
1answer
28 views

Stored procedure deployment - push or pull into database? [closed]

Using stored procedures as part of an ETL process to populate tables in another database, which method would be considered a best practice (if any) in terms of deployment, maintenance, visibility and ...
1
vote
1answer
75 views

Items and their tags

This is my table design: CREATE TABLE IF NOT EXISTS `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `description` mediumtext NOT NULL, ...
0
votes
2answers
58 views

How to export Oracle database without data

I want export my Oracle database with its structure such as tables, schema, users, tablespaces, datafiles and everything but without the contained data.
0
votes
0answers
34 views

Chicken and egg problem with JOIN tables and memberships

So I'm having a problem where users are able to access certain models that depend on a join model that doesn't exist at the time of the access. Quick overview of the use case: Basic school ...
1
vote
3answers
105 views

SSIS: How to check if a record DOESN'T exist in flat file but exists on the database

I am working on preparing an SSIS job where I am importing a .CVSV file to OLE DB destination (sql database). We are going to get these files on daily basis. The .CSV file contains records of doctors. ...
1
vote
1answer
83 views

What is a suitable,stable, high performing database for Large Financial Time Series Data? [closed]

I realise this question may have be answered in some form but I would like to gather the thoughts and opinions of actual users with experience in this field of Database design and development. I have ...
1
vote
0answers
62 views

Ticket booking engine

I am going to build and flight ticket booking system. My client has own planes. Under is basic concept of engine. Admin will add flights. Passengers,agents,franchises or admin will book the tickets. ...
3
votes
1answer
50 views

Schema for scarce goods

I have following objects: (Rooms) which are (available in a certain period of time [in days]). During the time of availability they can be (booked) by (users). There are basically these four tables. I ...
5
votes
3answers
214 views

MySql Invoice and Subscription Schema

I have an ecommerce db built and I'm adding subscriptions. See the simplified structure below. orders id, customer_id order_items id, order_id, product_id invoices id, order_id subscriptions id, ...
0
votes
0answers
30 views

Looking for feedback on a design for a simple task manager

I've got tired of using notepad / excel to manage my tasks at work. Since tasks may sometimes contain sensitive data, my boss will not allow us to use an online task management system, so I've decided ...
4
votes
1answer
77 views

How to model “Courses prerequisites” for a university?

I am trying to model a courses structure and dependencies of a university. For example, in the Computer Engineering department you need to do Data Structures before doing Artificial Intelligence. But ...
1
vote
1answer
51 views

DB design for employee working hours constraints [closed]

I have a simple employee schedule database (I use PostgreSQL): employees: - id - name - email shifts: - start - stop - employee_id Now there is need to apply constraint for working hours. ...
1
vote
2answers
38 views

Include logical key on each table vs. join

I'm designing a database that is used to track an inventory item through its lifecycle. I'm currently relating my tables using the previous lifecycle event's ID (for example, a shipment contains the ...
0
votes
1answer
43 views

How to get last messge between two friends in the messge table having id in descending order?

Hi friends help me to solve this problem ," display all last messages of a user from all his/her friends" My sample message table is --------------------------------------------------- | id | msg ...
2
votes
1answer
104 views

Personal finance database design

I'm making a personal finance management application in PHP and MySQL, for learning myself some more PHP and MySQL. This system is currently using 4 tables: users, this table holds all the user ...
8
votes
3answers
128 views

Why NOT partition?

When would one NOT want to partition a database? (thinking MySQL partitioning) In my case I'll be starting with a couple millions rows, it should grow from there. Primary key on a character field ...
3
votes
1answer
131 views

Storing created/retired dates for a large amount of different entities

In the db I'm working on now almost every entity has these 4 columns: CreatedDate CreatedBy RetiredDate RetiredBy Usually this is used for logging purposes, and for some of the entities the ...
0
votes
0answers
38 views

What happens to the database structure when i add a nonclustered index

Our team is working for a while now with Azure SQL. After a lot of trying , we managed to add a sync group between the main DB and the backup one. Of course, this means that for every change in the ...
0
votes
0answers
39 views

MySQL Db design: Optional data in separate table or as NULL values in original table

I am having following problem: User fills out the form, which consists of two sets of fields - header (basic information about biological observation) and detail (measurements and advanced ...
0
votes
1answer
36 views

I am designing a database for a game and the only option I can see a multiple table solution. Is there an option that I'm not seeing?

I searched but could find a similar situation, if I missed it please let me know. In this game there are approx 14,000 items which have their own table. The players will want to build multiple ...
1
vote
0answers
20 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 ...
1
vote
1answer
38 views

Database design for multi foreign keys

For example, I have two tables: Question(QuestionID, QuestionTitle, QuestionContent) and Answer(AnswerID, QuestionID, AnswerContent). Now, I have a Vote table that support vote for both Question and ...
2
votes
1answer
77 views

Database Design - Removing ambiguity without adding loop

I'm looking for a better approach to the problem (and my solution) described below. I've attached an image of my initial design Let's say I have a cat, and I'd like to find its owners and what vets ...
1
vote
0answers
33 views

Design optimization

I'm designing a database for a crm like software. Actually it's my first project so i'm learning on the run on this one. I have customer and employee tables. I also have a history table for tracking ...
3
votes
3answers
118 views

Redesigning a table that has a lot of columns (not always related)

I'm new to database stuff so I apologize if I say anything ridiculous.. I work for a company that runs a website for a certain industry. We're not very large at the moment but our parent company ...
0
votes
0answers
33 views

Where to put the currency field?

Abbreviated I have following tables: Companies own flats and rent them for specific periods_in_time and lastly a currencies table. The price column is sitting in the periods_in_time table. But where ...
1
vote
0answers
52 views

Get all data from two tables and group it into different category in mysql

These are my tables to build an FAQ: CREATE TABLE `tbl_section` ( `section_id` int(11) NOT NULL AUTO_INCREMENT, `section_title` varchar(100) NOT NULL, PRIMARY KEY (`section_id`), ...
0
votes
0answers
24 views

Multiple table relationship for different scenarios!

We have the following heirarchy in our platform; Package is comprised of Products, Products is comprised of Services and Services are comprised of SKUs. The relationship between them is many-to-many ...
1
vote
1answer
19 views

communication followup database design

I'm trying to decide on how to set up the tables to get this use working without over-complicating it or not collecting enough data. For the sake of an example, I've got a ticketing system with two ...
2
votes
1answer
23 views

column identifier versus separate table with related data with different purposes

Very new to database management here. I'm trying to figure out what rule of thumb I should be following when it comes to making separate tables for data rather than just labeling it as different ...
2
votes
3answers
71 views

Schema for a lease platform

I have following tables: A room I want to lease (has user.id as ForeignKey) A users table for users who can rent it An availability table for time periods the room is to use with prices for that ...
1
vote
3answers
168 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, ...
2
votes
1answer
123 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 ...
2
votes
1answer
40 views

How to relate a table to other two?

I'm trying to design a database that contains, among others, entities "Photo", "Venues" and "Products". Given both Venues and Products could contain a collection of photos... how could I tell whether ...
1
vote
1answer
110 views

Is it ok to create additional column for helping other column to be a PK?

Let's say a Person can have more than one nickname. He(she) must enter his(her) nicknames alongside with other details: Here is the table columns: User_Id, Nickame, Age Of course, for now there ...
1
vote
1answer
41 views

Using a GIN index on an integer array to model sets of tags

In PostgreSQL, what are the pros and cons of using a GIN index on a column of type integer[] to model sets of tags, instead of doing a relation table? I am trying to design a simple data model with a ...
6
votes
2answers
184 views

nvarchar(max) converting to varchar and table optimization

I am working with a table that has all character types set to nvarchar some of them are nvarchar(max). We are converting all these to varchar and specifying a character width based upon the actual ...
2
votes
1answer
79 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, ...
0
votes
1answer
49 views

How to track changes to a record that spans multipe tables

I have the challenge to design a new database using a RDBMS, Oracle, to be precise. In this database there will be one specific record/document which may span multiple tables. With as little ...
1
vote
1answer
42 views

Product attribute schema design, mixing solutions

I am working on a product database in MySQL and the products can be multiple types with different attributes. The number of attributes and there datatype vary. I read about some design patterns (post) ...
0
votes
1answer
45 views

Multiple Cascade Paths (Not really) - Sql Server

In my database I have a table with 2 columns referencing the same column in a separate table. I know this creates a race condition, but I have a check in place to make sure they don't point to the ...
2
votes
4answers
74 views

Row for each date, or 31 columns?

Currently I have a system whose front-end looks like this: The following query: SELECT `l`.*, `s`.`month`, `s`.`year` FROM `summarylines` `l` JOIN `summaries` `s` ON ...