The logical model and/or physical design of a database.

learn more… | top users | synonyms (4)

2
votes
2answers
201 views

MySQL - unique constraints design

I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community. Please consider the diagram: I would like to add a unique ...
2
votes
2answers
2k views

Should I use a separate table to store image file names?

I am learning about database design and I'm writing a Java GUI program with a back-end database. The main table in my database stores a different product in each row. I have some columns such as: ...
2
votes
3answers
406 views

Database design for conditional detail table

I have an expense management system for monthly expenses. Expenses are of two types: one-time monthly expenses, and daily expenses for each day of month. I have created the following tables: ...
2
votes
3answers
2k views

Is it worth to separate columns into multiple tables for one-to-one relational table

I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table. ...
2
votes
3answers
3k views

Simple scenario needs to go from Unnormalized Data to 3NF - Review Please

NOTE: The Question has been updated. View Edit Revisions to view original question. The scenario. An animal charity that at the moment, track their establishment's costs via a spreadsheet like so: ...
2
votes
1answer
807 views

How should I model a binary-tree like data using Adjacency List?

I'm making a system where a user can recruit others, resulting in a binary tree model of users. I have decided to use Adjacency List to model the data but I have some doubts about the number of fields ...
2
votes
1answer
445 views

Setting up simple question & answer database

I am having trouble thinking this (simple) table relation through. I am setting up a quick SQL DB to populate labels and textboxes dynamically on screens. This is just to prove we can do it, so it ...
2
votes
1answer
506 views

Database structure for affiliate system

I am creating affiliate tracking system and looking for best database structure to use on MySQL to place the lowest possible load on the server. There will be 1000 affiliates, and each will have ...
2
votes
3answers
6k views

Software tool to visualize and print DB diagrams [closed]

I have some SQL Server databases and use SQL Server Management Studio to manage them. However, I find that is not very friendly to create, design and print database diagrams (for existing tables). ...
2
votes
1answer
541 views

DB stored procedures: how do they help increase security in web systems?

The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic ...
2
votes
1answer
264 views

From a design perspective, what is the difference between an inventory system and an asset tracking system?

From what I can see, the database design is similar. One is the consumables that are resident in a warehouse (inventory). The others are items, capital, that are assigned to people or places, but ...
2
votes
1answer
390 views

Schema for 1 <-> 0..1(+), many related but only one primary

Using tools available in SQL Server 2008 and Entity Framework, what is a solid way to represent a one-to-many relationship, where there is an underlying unique relation that I need to enforce? My ...
2
votes
5answers
273 views

How to shard 14 gb of data for archival database?

We have 14 GB worth of CSV's containing product information, with each CSV being all the records for a US state. We now want to store this in a database. There would be ~130 million records. The ...
2
votes
1answer
850 views

How to store several lists and their items

The app I'm working on revolves around projects. Each project can contain several lists, and each list is made up of several items. Each list relates to a single project. Is it better to have one ...
2
votes
1answer
143 views

One table or several tables?

I have several types of writings: articles, projects, reviews, etc. I have a table containing paragraphs (fields id (INT) and partext (TEXT)). Each writing consists of several (ordered) paragraphs. ...
2
votes
1answer
955 views

Modelling an E-R diagram for a Pizza place database, need help representing the payment method for the customer

I am modeling a "pizza place" and a customer has an account info. I created a separate table for account info. In here a customer will have an amount spent and method of payment attributes. The ...
2
votes
1answer
73 views

Single field instead of several fields

There is a relational database with the following Users table User Id Name Age Parents Friends Colleagues ... And there are tables Parents, Friends, Colleauges, etc. Due to specifics of database ...
2
votes
1answer
251 views

How can i model this data

A customer can make a reservation for an accommodation, the accommodation can be in a resort or elsewhere. So i would say that a resort can have different types of accommodations, ie. Chalets, ...
2
votes
2answers
232 views

design of maintenance database

Lets say i am designing a database that tracks maintenance of equipment. Each piece of equipment has various fields that usually don't change eg. ID, model, serial number etc. Some fields change ...
2
votes
2answers
102 views

Is it safe to use “Safety” as a schema name?

I was considering using "Safety" as a new schema name in SQL Server. In SSMS, the term Safety is colored as a keyword, but this term is not listed as a reserved keyword in the BOL. The database does ...
2
votes
1answer
99 views

How to make a duplicate field cheap?

There are two tables associated with each one to many in a field. In Table 2, there are fields that duplicate the value in Table 1. Is there a cheaper way to ensure (guarantee) the correspondence ...
2
votes
1answer
1k views

Track daily view count of youtube videos

I am making program that searches youtube for given keyword and need to get links of active videos. Term "active" means that video must have 1000 views per day. Youtube API doesn't give such ...
2
votes
2answers
88 views

Importing order information

Our new project is to write a customer order management system. My specific task is to import customer orders from various different sources - XML, CSV, XLS... etc. Our old DBA has left the company ...
2
votes
1answer
734 views

Question on Design for User + User Groups + User Types + Subscriptions Model

I am redeveloping an existing web application with both weak source code and a weak underlying database design. I'm trying to develop things in a way that future expansion will be much easier, ...
2
votes
2answers
42 views

In a one-to-one relationship, where should the foreign key be placed?

I am currently designing an ERD for a project management system. I am unable to choose between two workable solutions as the best solution as I wish to embrace the best standards. The two entities ...
2
votes
2answers
55 views

Multi level hierarchy relationship

My table design is users (id, username..., parent_id) Data is id username parent_id ------------------------------- 1 | admin | null 2 | reseller 1 | 1 3 | client 1 | 1 4 ...
2
votes
1answer
25 views

Turn off auto qualification of table names when creating a view

Problem Definition I am trying to modified a shared development database, for a dev team to use for application development/testing. Most of the collective work (table names, views, etc) are stored ...
2
votes
1answer
71 views

How to maintain Database audits?

I am using MySQL database (with Django ORM). I want to maintain the database audits similar to StackOverflow, Quora, Wikipedia, etc. These websites maintain the revisions of the changes in the ...
2
votes
1answer
34 views

Table attribute for sorting

I have 3 Tables, a User table, a Tracks table and a Likes table. Users can like as many tracks as they want and one track can be liked by multiple users. In my Likes table I have two foreign keys to ...
2
votes
1answer
46 views

Constraining foreign keys to two parent tables

For the model above I want to make it so a robot must have a power supply and motor with matching voltages. Is there a way to constrain the robot entity so it cannot have a power supply and motor ...
2
votes
2answers
115 views

Best data modeling approch to handle redundant foreign keys in relational model

I am looking for advice on the best relational modeling approach to store surveys, questions, and responses. I am looking for which of the two approaches below looks best, or an alternative approach ...
2
votes
1answer
37 views

Finding a good DB design for my project

I've posted this on stackOverflow but this might be a more suitable place. I'm really struggling to find a good DB design for my project using SQL server. I've already implemented a few models which ...
2
votes
2answers
105 views

Database design for web mail client

I am implementing a web-mail application.Only registered users can send, receive and view E-mails. When I start to design the database,I came up with following issue. Lets say this application has ...
2
votes
2answers
59 views

Table “normalization” and tradeoffs

It's been a while since my databases course in university, so bear with me as I try to be unambiguous here. In the domain I'm working in, there are Sources and Destinations. Both have 2D coordinates ...
2
votes
1answer
43 views

Design for user profile information and track changes

I have a web page with a login functionality. Logging in is based on email addresses. Also, I have to keep basic user profile information like: street city zip code mobile phone country contact name ...
2
votes
1answer
55 views

Best DB approach for a lot of updates

Recently I built an app that gets that from SVN repositories to run a few metrics. What I do is basically: 1) Read data from an existing SVN repository and save this data to a database (with only ...
2
votes
2answers
295 views

Database design for a blogging site

I would like to know if this is a good database for a blog: This is a data model for a blog system that has the following characteristics: Users can register Users can have different roles, eg. ...
2
votes
1answer
132 views

Foreign Key Constraint over two tables

I am trying to put the following entity-model into practice: We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in ...
2
votes
1answer
67 views

Migrate existing SQL Server database file to smaller files

I have a database that is about 1TB in size. It's currently on a 2TB hard drive and I would like to replace it with a few 512GB SSD. I need to split the existing database file (.mdf) into multiple ...
2
votes
2answers
78 views

Three-sided many-to-many relationship design

I have 3 entity that have a relation to each other. My entities are: OPTION PLAN PRICE Relationships: PLAN and OPTION have a many-to-many relationship Map of PLAN and OPTION has a many-to-many ...
2
votes
2answers
64 views

Database design appoach confirmation

I am working for a mortgage company in migrating a legacy application to new application for managing claims, means lender fore close a loan and request for a claim to get its funds from government or ...
2
votes
1answer
446 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 ...
2
votes
1answer
89 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 ...
2
votes
1answer
46 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 ...
2
votes
1answer
67 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 ...
2
votes
2answers
148 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 ...
2
votes
1answer
67 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 ...
2
votes
1answer
103 views

Table structure for voting app

I have an app where a user votes between two local profiles on which one is better. I want to ensure that the same user cannot vote more than once between the same two profiles (say the user votes on ...
2
votes
2answers
957 views

Many columns vs few tables - performance wise

Yes, I am aware that data normalization should be my priority (as it is). I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth, ...
2
votes
2answers
62 views

unusual relation, binary aspect

I have question very unusual for databases. I’m not a good English speaker, but good English reader :) so for explanation I’ll give you examples. Part1 In MySQL, I have two tables with relations ...