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)

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
2answers
4k views

Database design for an E-commerce website

I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand ...
3
votes
1answer
30 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 ...
0
votes
0answers
35 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 ...
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
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 ...
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 ...
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 ...
1
vote
0answers
63 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. ...
1
vote
1answer
55 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. ...
0
votes
0answers
31 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 ...
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 ...
22
votes
4answers
55k views

How do I list or search all the column names in my database?

I want to search for a string in the names of the columns present in a database. I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking ...
0
votes
2answers
441 views

Storing a music playlist, best approach?

I am working on a web project that creates playlists online based on youtube links. My question is how do I store a playlist on MYSQL. What I was thinking about doing was having a table with ...
2
votes
1answer
107 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 ...
7
votes
1answer
1k views

Best practice for storing record metadata

What is the best practice for storing metadata of individual records in a database? I need to store common meta data such as creation time and time of last update for many tables in my database. I ...
22
votes
6answers
8k views

What problems will I get creating a database per customer?

I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases. We are just ...
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
3answers
172 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, ...
16
votes
5answers
6k views

Plural vs Singular Table Name

How should I name my Tables when creating a new database? Singular: Client or Plural: Clients?
1
vote
1answer
39 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 ...
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 ...
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
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
119 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 ...
1
vote
4answers
383 views

Database/Storage engine suggestion for a project involving heavy inserts?

I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities ...
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
54 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`), ...
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 ...
0
votes
0answers
26 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 ...
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 ...
6
votes
2answers
191 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 ...
4
votes
1answer
525 views

Removing “duplicates” within a time range

I have a table that contains number plate data with a date and time stamp, collected by a plate-reading CCTV camera at a site entrance. Vehicles stopping at the site entrance will create multiple ...
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
111 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
43 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) ...
2
votes
4answers
75 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 ...
1
vote
1answer
42 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 ...
0
votes
1answer
46 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
1answer
82 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
51 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 ...
0
votes
1answer
367 views

Trigger function check if all NEW.* fields exist in another table

In PostgreSQL I need to code a trigger function that checks if all the fields in NEW.* exist in another table that has the exact same name as the table that fired the trigger but appended with "_hv". ...
0
votes
1answer
57 views

Usage of json to store multiple attributes

Typically we have the table designed like this UserId FirstName LastName EmailId MobileNumber Age Sex City Where UserId is going to be the primary key Is it good to have multiple columns ...
5
votes
1answer
191 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 ...
2
votes
0answers
52 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 ...
0
votes
2answers
76 views

Managing tables in database

I am new to web development field and particularly very poor at database management. I am developing a web application which resembles Project Management System where new companies can add their ...
0
votes
0answers
15 views

Fill the 5th table with 3 other tables data in LibreOffice

I have 5 tables primary key foreign key felh(felh_id, felh_nev) gepek(gepid, felh_id, gep_nev) lic(lic_id, lic_k, lic_l, lic_db, gep_id) pr(pr_id, pr_n, pr_vr, lic_id) haszn(haszn_id, gep_id, pr_id, ...
4
votes
2answers
43 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 ...