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

1
vote
2answers
571 views

Database Design - Employee Clocking In and Out System

I want to develop Employee Clocking In and Out System (website). I am concerned two things: If staff forgot to 'Clock Out' from yesterday and they have 'Clock In' today, it should flag to the ...
1
vote
0answers
247 views

Entity relationship diagram [closed]

What is the difference between ERD 1 and ERD 2? From what I understand is that ERD 2 has the many to many relationships resolved with a link entity whereas ERD 1 only shows the relationship... is ...
1
vote
2answers
334 views

How to model double relationships?

Background: I have designed a solution for Filemaker to input data into a Question Database. After we are done with inputting data into Filemaker, we will be exporting the data as excel and ...
11
votes
2answers
2k views

Storing massive amounts of data from a sensor array

I have been tasked to implement a solution (app and db) to store the data samples from a huge sensor array. The array currently consists of about 20,000 sensors, but that is soon going to grow, up to ...
3
votes
6answers
20k views

How to export an ER diagram from an Oracle DB?

I am developing this application with an Oracle database. I haven't been documenting the data model, but now an auditor wants to have an entity relationship diagram of my database. There are many ...
1
vote
1answer
99 views

Impact of changing schema name

I have a server with two databases. Each database has a different schema name despite the fact that DB2 is a subset of DB1 (DB2 has a subset of the tables in DB1). Not withstanding any code changes ...
2
votes
2answers
438 views

Joining based on date (or date range)

I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)): staffid | target | ...
5
votes
3answers
591 views

how do I model this in database?

I am using mysql as my backend database. Here is the use case; A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. ...
1
vote
1answer
104 views

Right Distribution of Databases amongst Multiple Instances

So it's obvious that many times you'll have multiple instances (even if on the same server) to have a logical separation of databases for security purposes. But when does it make sense on the ...
4
votes
2answers
480 views

Separate Address Table, Latitude, Longitude

We are designing a DB for a limited functionality social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each ...
1
vote
0answers
414 views

How to customize Place Order Functionality In Magento? [closed]

In Magento, when any customer buys product from front-end, then at last customer goes through order placing. According to my need, I have created one CUSTOM TABLE in Magento database. So my ...
0
votes
1answer
299 views

Database Design: What is the best structure for storing site offices and locations?

Background I am creating a database driven web application for the management team at the place I work. Part of this application requires fairly specific locations to be linked with where equipment ...
11
votes
4answers
7k views

SQL Server database on an SSD - any advantage to a separate file for every table?

I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
7
votes
1answer
977 views

Schema designing for handling multiple payment gateways

This is more of a question that requires feedback. I'm designing a database that handles multiple payment gateways. A payment gateway mostly requires a table for order details before making the ...
1
vote
1answer
260 views

Referring to super parent or as a separate entity?

I have this person table as super parent, id firstname lastname email telephone ... ... and user table as a child id person_id (FK) password username screenname ... ... They must be 1:1 ...
5
votes
2answers
2k views

how to enable temporal database capability in mysql.?

How to enable the temporal database compatibility in mysql database? Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current ...
8
votes
2answers
1k views

Database redesign opportunity: What table design to use for this sensor data collection?

Background I have a network of approximately 2000 sensors, each of which has about 100 data points that we collect on 10 minute intervals. These data points are typically int values, but some are ...
8
votes
2answers
668 views

Why shouldn't I have one table for multiple relationships?

Assuming I have multiple relations in my database, for example Store, Employee and Sale, and I want to connect pairs with a simple binary relationship. Personally I would create tables named ...
2
votes
2answers
103 views

Adding Security to a legacy DB with Schema

I am working on a DB where almost every technical user has full read / write permissions on all tables. We are working towards removing permissions, gradually, from certain users, and I had hoped that ...
7
votes
4answers
829 views

Are there any good reasons for keeping date and time in separate columns?

I'm trying to understand our software vendor's decision to keep date and time in separate columns. For example, when the row was created or updated. Both time and date are DateTime columns. We are ...
5
votes
2answers
787 views

How to design a schema to store search keywords

I am doing a database design that stores search keywords of a search form, used to generate the most popular search keyword reports by date, month and year. I have made a simple schema: CREATE TABLE ...
11
votes
3answers
3k views

Plural vs Singular Table Name

How should I name my Tables when creating a new database? Singular: Client or Plural: Clients?
6
votes
2answers
1k views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
5
votes
3answers
2k views

SQL Server Primary key / clustered index design decision

Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database. I've got a product history ...
9
votes
4answers
1k views

How to store data in a database without tables?

All I learned in school was SQL which saves data to tables. Right now I am working on a project where data is stored in XML-files. Additionally every XML contains a reference to visual files (JPEG). ...
7
votes
3answers
343 views

Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
4
votes
3answers
243 views

How do I add a time dimension to this forecasting database without having to duplicate every data point @ each reforecast?

I have a database designed to facilitate revenue forecasting. Revenue forecasting consists of tracking Orders which are recognized into Revenue at multiple points in time (each, a revenueDate). There ...
6
votes
2answers
437 views

Using 100's of schemas in a database SQL2008R2?

We are in SSAS type of environment with 100's of clients. Currently each client gets a database and we have 200 per instance on SQL Server 2008R2. This presents limitations with snapmanager being ...
4
votes
3answers
483 views

How do I model data from arbitrary CSV's in a database?

I am setting up a web interface for data management. Users upload CSV or similarly structured files, and I want to store these in the database so they can do manipulations on them -- filtering, ...
2
votes
2answers
794 views

Is to possible to forbid access to tables in own schema - Oracle?

Lets assume that we have the following situation: We have an Oracle schema/user: USER_TEST (with the "create session" privilege). SYS as SYSDBA creates couple Tables on schema USER_TEST. Is it ...
0
votes
2answers
156 views

How to implement Draft functionality, with attached files?

Should allow for: User can save an entity as a draft, that would normally not be valid in its table (ie some missing values) User can attach files to the draft User can find, sort, and discard their ...
5
votes
2answers
1k views

How to implement soft deletes?

What is your method of implementing soft deletes in a database? Ideally the solution would allow for decent performance on large tables take relationships into account take unique keys into account ...
8
votes
1answer
2k views

Generating Invoices and Tracking

Every 2 weeks, the system will generate the invoices for the companies. Company will receive an invoice on the 1st and 16th every month. (It will run via Cron Job every 2 week. It scan through the ...
2
votes
1answer
659 views

Table structure/schema for spreadsheet-like web app (ex: Google Docs)?

I'm a front-end developer/designer who is trying to branch out and learn more about the back-end world. I've chosen Python, Django, and Postgres as my starting place. My goal is to build a personal ...
9
votes
4answers
2k views

Is it a good idea to use Synonyms to avoid creating a duplicate table?

We have 3 copies of the exact same database. All 3 databases have a Users table, and a User will always exist in all 3 databases with the exact same settings. Anytime we want to add or edit a User we ...
4
votes
2answers
1k views

Ready-to-Use Database models example

Where can I find ready-to-use database models ? I don't need a database with data in it, but only schemas (UML diagrams). Perhaps something like the data models at this link, but much more complex ...
15
votes
5answers
251 views

Enforcing database integrity

Would this ever make sense to have application enforce the database integrity instead of having foreign keys, check constraints, etc? How much of a performance improvement one can expect for not ...
1
vote
1answer
159 views

Tick boxes and Dropdown

Have a look at the image below: This is "Plain Buger" item - customer can select sauces and extras with it. There are different sizes for Plain Burger. I need to translate this UI into database ...
3
votes
2answers
1k views

Having a unique ID for 2 different tables

We would like to use two different tables, one will hold an object when it is 'Active', and the other will hold the object once it becomes'Non-Active'. The ID is therefore unique per both tables ...
0
votes
1answer
439 views

Some Ideas In Database Design

I am creating a site where users can sell their products(let say a little version of eBay, just assumption). Concerning the database design am a little bit stuck and looking for some ideas how it is ...
1
vote
1answer
206 views

Do you feel like DBA/DBD Is a “secure Field” still? [closed]

I noticed recently alot of Database jobs/Development have kind been pushed onto general developers, not to mention with NoSQL Solutions (Which IMO are probably a little easier for Non-DBA's to throw ...
-1
votes
2answers
698 views

Multiple child tables

I would like to know what is the best solution for making a parent table with multiple children? I have to make multiple children table. One for each profile. Is there a better idea than having 3 ...
4
votes
1answer
57 views

Want to add a “variable” to a table

I'm using PostgreSQL 8.4 and I have a table that is cleared out and refilled with new data every so often. I want to be able to store the date that the table was last filled, but I'm not sure where to ...
15
votes
2answers
365 views

Is it more secure to go through a 3rd database to connect two databases using the same login?

We have the following setup: Multiple production database containing private data which is used by desktop software A web database for a public website which needs some data from the private ...
2
votes
3answers
375 views

Express union which is dependent on other results

I would like to make a union of two tables that are actually in a hierarchical constellation. How would I write that in relational algebra? Say table A is the parent of B in a 1:n relationship. ...
2
votes
1answer
442 views

Design problem with auto-increment fields

I've made this design similar to my problem, it's not exact but it close enough to understand my problem: http://i42.tinypic.com/bhbp5.jpg Some cellular client frequently send their GPS position and ...
9
votes
2answers
1k views

What are the penalties of using many (thousands) of column families or keyspaces in Cassandra?

I am in the process of evaluating the best design for our Cassandra installation. There is not so much information out there on the Internet about using the first two levels of access that Cassandra ...
4
votes
1answer
2k views

What is a basic model for making a database with users and groups?

I'm trying to find the best way for a basic security system for a website. I know I want users and groups. I thought I'd have: user_table user_id user_name ... group_type group_id group_name ...
1
vote
1answer
516 views

SSAS Modelling - many to many with one dimension

I'm having bit of a hard time modelling an OLAP cube. My analogy is this: I have a list of students. Each one has one Major subject of study, and zero or more minors, up to five in total. The raw ...
9
votes
6answers
6k views

Splitting Tables in MySQL. Good practice?

I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data. The tables look like this: ...