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

learn more… | top users | synonyms (4)

5
votes
2answers
188 views

Design for storing data only applicable to a small subset of records

So my boss and I are in disagreement how to best design our database for storing miscellaneous data for certain clients. Basically, we have a bunch of tables in some format similar to this (in pseudo-...
13
votes
4answers
608 views

How much of a security risk are published conceptual schema?

I was requesting the conceptual schemas from a government agency's information system for my research. My request has been denied on the grounds of it being a security risk. I don't really have ...
3
votes
1answer
6k views

DROP SCHEMA in db2

How do I drop a schema in db2 if it has got some objects in it? db2 DROP SCHEMA SCHEMA_NAME For some reason I am not able to drop the tables in the schema, so how can I force to drop the ...
2
votes
1answer
505 views

Oracle ash report

Recently, i have been handed over with an ash report from DBA. To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
1
vote
2answers
1k 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
2answers
535 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 ...
13
votes
2answers
4k 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 ...
4
votes
7answers
46k 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
115 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
2k 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
772 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
105 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 ...
5
votes
2answers
712 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 ...
0
votes
1answer
510 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 ...
14
votes
4answers
10k 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 ...
8
votes
1answer
2k 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 ...
3
votes
1answer
462 views

Implementing a one to one (1:1) relationship between Person and User

Scenario description I have this person table as super parent (or supertype), id firstname lastname email telephone ... ... and user table as a child (or subtype) id person_id (FK) password ...
5
votes
2answers
3k 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 ...
13
votes
2answers
2k 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 ...
10
votes
3answers
1k 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
108 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 ...
8
votes
4answers
1k 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
2k 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 ...
20
votes
5answers
10k views

Plural vs Singular Table Name

How should I name my Tables when creating a new database? Singular: Client or Plural: Clients?
7
votes
2answers
2k 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 ...
6
votes
3answers
4k 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 ...
10
votes
4answers
2k 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). ...
10
votes
3answers
910 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: ...
5
votes
3answers
362 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
502 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 ...
5
votes
3answers
1k 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
2k 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 ...
2
votes
2answers
74 views

Best index for this scenario

There is a table in our database tblCustomText( ParentID int NOT NULL, CustomFieldID int NOT NULL, Value char(50) NOT NULL, Log_CreateDate datetime NOT NULL ) (ParentID+CustomFieldID) is ...
0
votes
2answers
278 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 ...
6
votes
2answers
3k 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
6k 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
1k 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 ...
13
votes
4answers
3k 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 ...
11
votes
2answers
4k 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 ...
17
votes
5answers
287 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
166 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
2k 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 (...
1
vote
1answer
698 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 ...
2
votes
1answer
305 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
2k 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
59 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 ...
16
votes
2answers
384 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
501 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
525 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 ...
10
votes
2answers
2k 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 ...