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.
5
votes
1answer
220 views
effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried
I am looking for advice on table/index design for the following situation:
i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
4
votes
1answer
154 views
SQL Server database design for “archived but available” data
We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice.
...
4
votes
0answers
70 views
Does PgBouncer create a separate pool for every database config entry?
If I set up a pgbouncer.ini config file with several database entries, does pgBouncer create a separate pool for each one? I would think so, but have not been able to confirm this.
For example, ...
3
votes
0answers
90 views
Database table design question
Firstly, database novice here. I'm trying to create a system for processing Sales Orders into Purchase Orders and then split the Purchase Order into Shipping Containers. The systems needs to be able ...
3
votes
0answers
102 views
Where and how to store my weird datamart
I could really use some help here.
Here's my situation.
I'm building a datamart in SQL Server 2005, which will furnish a single report (for now). We have 26 clients (medical organizations) that ...
3
votes
0answers
23 views
Does avg_query for PgBouncer include update and inserts?
Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage:
avg_query
Average query duration in ...
3
votes
0answers
105 views
Database describing databases and references to tables
Context
The company I work for makes a number of different softwares each with its own database. As they're all about related things (accounting) a good amount of data is theoretically the same among ...
3
votes
0answers
187 views
How to design a database for financial bond prices?
I have following flat file table composed of bond deal names and asociated prices.
Each deal name is identified by its unique CUSIP and ISIN codes and each deal name is classified with a certain ...
2
votes
0answers
38 views
Replicating databases for local and cloud instances
Forgive me if this is an inappropriate place for this architectural question but I'm not sure where it best fits in the StackExchange family.
I have been tasked with developing an online/live auction ...
2
votes
0answers
47 views
Two different ON DELETE paths, how do I need to change the schema?
For a project in school I'm designing a website that sells items via auctions. Think eBay, but on the complexity scale of school project. We went through the process of making an ER Diagram and ...
2
votes
0answers
72 views
How to properly configure pgBouncer transaction level pooling?
What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
2
votes
0answers
63 views
Digital asset management
I'm working in a company that currently develops Media Asset Management system and now we want to improve our system for document files. I have to search nearly thousand .doc and .pdf files and index ...
2
votes
0answers
138 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
0answers
576 views
Schema for an RBAC (Role-based Access Control System)
I want to implement an RBAC, Flat based. I've decided to base my system on the NIST standardization model.
Although all fine and well, I have an inclusion to that design.
Instead of a hierarchy ...
2
votes
0answers
249 views
How to translate high level entity relationship into schema
According to the following articles written by Dr. Chan, we can describe high level entity relationship. (see page-10,11 of doc-A and page9 of doc-B)
•doc-A) English Sentence Structure and ...
1
vote
0answers
34 views
Record versioning and promotion
Let's say we have this hierarchy:
-World
--USA
---WA
----Seattle
-----Downtown
------1st Ave
-------945 1st ave
------3rd Ave
...
1
vote
0answers
56 views
Database Design: separating reusable & instance data
I'm developing an app which is basically a collection of checklists that have to be processed. I obviously need a database in which the information has to be stored. I just can't wrap my head around ...
1
vote
0answers
28 views
Need help to design model with multiple rights
For a website, I have multiple elements that need to be moderated by many different users.
Until now, I have always used Role-Based Access Control (RBAC) model but I want a more flexible model. I ...
1
vote
0answers
54 views
Real Time data collection - Help select Database
My Scenario:
I want to collect data from various sensors[for now I have 600 sensors sending data, but this may have to scale up to 3000 more data sensors, all sensors send real time data] Now I have ...
1
vote
0answers
53 views
Unable to decompose this relation to BCNF
I have a relation
R = { A, B, C, D, E, F, G, H, I }
And functional dependencies
F ={
ABC -> DE
E -> C
AB -> F
C -> G
F -> H
H -> IJ
F -> B
}
I am able to do simple BCNF ...
1
vote
0answers
72 views
UML class diagramm for database design?
I used to make database designs using a french method called MERIS that uses mainly two diagrams to create the database (MCD and MLD), then i heard about UML and that is used also for databases and ...
1
vote
0answers
79 views
SSMS produces error if query returns too many rows
We are having an issue with a server that we linked to our sql server 2012 instance.
The server that we linked through an odbc or oledb connection is Pervasive SQL.
Selecting from the linked server ...
1
vote
0answers
111 views
PeopleSoft or Oracle App Designer Tool Alternative
We are using PeopleSoft app designer tool to manage and create projects, database objects - tables, columns, views etc in Oracle database. This tool has limitation that we cannot create tables/columns ...
1
vote
0answers
70 views
key value database
I have one object and a lot of data associated with that object, you can think of it as something like facebook timeline where we have user and user has a lot of posts.
I cannot store data in JSON ...
1
vote
0answers
114 views
Improving database design for category based site
I'm working on a site that gets about 30k visits a day and it makes use of a lot of queries which uses UNION's over about 45 different tables, these tables generally range from about 15k rows to about ...
1
vote
0answers
246 views
Implementing a family database using Access 2010
A while ago I asked this question about using Access 2007 to implement a family tree model. I had all the tables and relationships mapped out in a UML diagram but realised it was getting out of hand ...
1
vote
0answers
68 views
User defined content specific permissions with varying conditions. How to design tables?
I'm working on a privacy settings problem, which is proving to be cumbersome to design.
Essentially I have:
User created content
User can define access groups for content.
Access groups vary ...
1
vote
0answers
187 views
How do I auto-fill data in Microsoft Access 2003?
I am creating a Purchase Order Form at work using Microsoft Access 2003 so that it can be saved and archived in a database and reduce paper costs.
These are my current dilemmas:
I am unable to ...
1
vote
0answers
182 views
Entity relationship diagram
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 ...
0
votes
0answers
39 views
When to split a large table
I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc).
However, in my use-case, every single day, the table gets 2160000 new rows.
It's also crucial that ...
0
votes
0answers
8 views
Fine grained (milliseconds) temporal indexes in neo4j
What are the best practices for modeling fine grained timelines in neo4j? If I were to use milliseconds as the grain in the pattern described here and here would it prove somehow problematic and ...
0
votes
0answers
16 views
Change URL of Erwin data model HTML export hyperlink of www.ca.com to something different
The website URL of http://www.ca.com appears in every fact group (subject area) of our Erwin file. How can we make a global change to this URL in Erwin, so we don't have to update each subject area ...
0
votes
0answers
30 views
How to handle “many columns” in OLAP RDBMS
I have a fact that has around 1K different numerical attributes (i.e. columns). I would like to store this in to a column-oriented DB and perform cube analysis on it.
I tried to design a star ...
0
votes
0answers
23 views
In regards to GS1 barcode - what size DB field are used?
In regards to GS1 barcode - what size DB field is used or is the entire GS1 typically not stored in database, but rather processed in the application layer and stored in the database as a collection ...
0
votes
0answers
44 views
Database Schema/Structure advice
Well i am reconstructing an already created application and was looking to improve this, especially with in the database structure.
I want to start off with probably the biggest section and that is ...
0
votes
0answers
71 views
How to represent class table inheritance (current DBMS-specific way please)?
I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server.
I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
0
votes
0answers
46 views
Ternary Relations three attributes and an association
I have 4 tables:
Workers
Work
Workers Relationship/Work
Registration Time
I need to have organized the Workers Relationship with Work so you can make a ListBox organized.
Where each worker may ...
0
votes
0answers
37 views
MySQL Stored Procedure
I am developing a database for an eCommerce website, I have written the stored procedure for the following tables,
user_master,
authentication_master,
country_master,
public_master.
I have ...
0
votes
0answers
37 views
NoSQL approach: design table “scheme”
We need to store simple log data in a database, but we also need to get and filter data in realtime, based on 'user', simple 'tag' (ie. redis, memcache, php, mysql, etc.) and timestamp. We need to ...
0
votes
0answers
29 views
“Related search” trick in huge Hypertable database?
Several years ago, I've created search engine using PHP with MySQL (MyISAM)
The "Related search" pick 5 most similar search title.
The data has growing bigger in matter of days.
Each submission has ...
0
votes
0answers
32 views
Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logs
I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs.
All the primary keys are type serial (auto incrementing integer) and all have the name in the ...
0
votes
0answers
99 views
error in creating trigger in phpmyadmin
I have 4 tables:
event:
event_id(p.k) | uid | circle_id(f.k)
activity:
uid | performed_activity_id(f.k->event_id) | activity_type_id
follow:
follower_id | circle_id(f.k)
noiticication:
...
0
votes
0answers
59 views
Am I interpreting this design correctly?
I'm using Michael Durrant's design as a starting place for designing my own survey-like database.
With this design, is it possible to order answer's, by datetime, for a question for a survey_section ...
0
votes
0answers
75 views
CalDav: Is it possible to reproduce an subscripable .ics file in a database?
CalDAV is an WebDav extension specificaly designed for accessing iCalendar files (*.ics).
Those .ics files are subscribable as long as they are stored on a CalDav server. If the owner of that file ...
0
votes
0answers
30 views
HBase last versions kept depending on timestamp value or real time added?
In HBase you can set a minimum versions and maximum versions to keep value.
Does the timestamp control what will be kept?
Like, if I modify the timestamp of a row to 100, then after that, I insert a ...
0
votes
0answers
45 views
How to properly design this type of relationship
I am building a model with the following requirements:
Items may have attributes
Attributes may be of type: 'Choice', 'FreeText', 'Number'
I am not quite sure how to relate the item to attribute ...
0
votes
0answers
47 views
How to store massive quantities of machine generated data?
I am tasked with developing a system that stores massive quantities of machine generated data (syslogs) from a number of server farms and I am wondering what tools you fine folks use such instances. ...
0
votes
0answers
54 views
MySQL database design help
EDIT: also posted on stackoverflow: http://stackoverflow.com/questions/12148715/mysql-database-design-check-in-system
I am attempting to build a check-in system for an office building.
What happens ...
0
votes
0answers
95 views
Schema for Google Docs-like sharing
Am trying to design a nicely normalized schema for sharing entities in a database, with good referential integrity.
It should work similarly to Google Docs sharing.
Here is what I have so far:
...
0
votes
0answers
94 views
Oracle VPD Implementation?
Our team is implementing VPD into the already existing Schema. The application is currently used by one customer and in order to generalize the application we need to implement VPD in our schema. The ...
