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.
4
votes
1answer
93 views
Designing Simple Schema for Disaggregation of Demand Forecast
I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (example, Raw ...
1
vote
0answers
78 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 ...
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 ...
3
votes
1answer
157 views
Deciding between an IsDeleted/DateDeleted column or a separate History table?
We have a table that stores nothing but an AccountId, TagId, and DateCreated. Users can update the Tags on accounts, which adds or remove records from this table. No tag can exist more than once on an ...
4
votes
3answers
518 views
How to have a one-to-many relationship with a privileged child?
I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
7
votes
3answers
154 views
Choice- or ids-list- field representation
I wonder what is the best way to represent a user setting that could either take a value from a set (such as 'High', 'Medium' or 'Low') or be a list of IDs from another (or the same!) table (Many to ...
0
votes
1answer
85 views
Database suitable for data mining
What would be an appropriate / suitable database sans Oracle that would be suitable for the following conditions?
data mining
includes but does not limit to the following:
a. clicking on links
b. ...
0
votes
2answers
60 views
Academic Journals and Reviews for Database Systems [closed]
I want to start reading up on the latest advances in database technology. Can anyone tell me what are the leading academic journals on the subject?
Also, I have a friend doing a PhD in another area ...
2
votes
1answer
91 views
How to avoid Repetition in the Database Design?
I have currently the following database structure,
Retailers
-----------------------------------------------------------------
ID Name XXX XXXX XXXX XXXX XXXX XXXX
...
0
votes
1answer
187 views
Many to Many and Weak Entities
I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many to many relationship.
Example: An artist has an album (the album cannot exist ...
2
votes
1answer
155 views
SQL Server - storing ulong as primary key
I need to store ulong values as a primary key in SQL Server. Since SQL Server doesn't have a ulong datatype (or for that matter anything unsigned), the only way to go is to have column as ...
6
votes
3answers
190 views
Store a formula in a table and use the formula in a function
I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
0
votes
1answer
199 views
How to set up data entry in many-to-many database in Access 2010 with forms/subforms?
I have a simple two table database in Access 2010 tracking landowners and properties. They are related in a many-to-many (M:M) relationship with a link table. Forms for viewing data have been ...
5
votes
1answer
89 views
To Foreign Key or not to Foreign Key… (inet -> cidr)
Below is a very abridged version of a project I am working on.
CREATE TABLE cidr_block (
id serial not null unique,
block_def cidr primary key,
dhcp_server_id int not null references ...
0
votes
1answer
176 views
one trigger for multiple tables
i have a trigger in PostgreSql 9.1 that i want to fire on inserts of several tables. is it possible to have it affect all these tables instead of creating the trigger for all these tables?
i have 58 ...
24
votes
5answers
2k views
Is it OK to keep a value which updates in a table?
We are developing a platform for prepaid cards, which basically holds data about cards and their balance, payments, etc.
Up until now we had a Card entity which has a collection of Account entity, ...
0
votes
1answer
70 views
Looking for a scalable relational database [closed]
I'm looking for a database that achieves the following:
fully ACID.
relational with joins.
has transactions.
allows the administrator to specify sharding rules (for example: users from Europe should ...
1
vote
3answers
362 views
Coding an accounting database from scratch?
My new medical practice is picking up fast and my homemade Libreoffice Calc spreadsheet is quickly becoming cumbersome to track patient visits, accounts receivable, overhead reconciliation, etc. Most ...
5
votes
3answers
391 views
What are the advantages and disadvantages of storing binary data in a SQL table vs a network drive? [duplicate]
Possible Duplicate:
Files - in the database or not?
I've been reading "Programming Entity Framework Code First" ...
5
votes
2answers
82 views
Relationship that are optionally more specific
Forgive my title, I couldn't think of anything that accurately describes what I'm talking about.
I currently have the following relationship.
CREATE TABLE Events
(
ID INT IDENTITY(1,1) NOT ...
0
votes
2answers
319 views
Open source alternative to SQLMaestro for PostgreSQL
I'm looking for oen source alternative to SQLMaestro for PostgreSQL because it has visual database designer which not avaliable in pgAdmin which make database desinging so easy.
PS I'm not sure if ...
3
votes
1answer
128 views
Outer Join vs Dummy Rows
I found that in a dimensional layout, it is beneficial to create a dummy row in the dimension to avoid writing LEFT OUTER from the fact table. There are other reason for this aside from perceived ...
4
votes
1answer
254 views
Is that a good idea to use one database for 50.000+ shops?
I know Shopify use only one database for all shops.
But how can they handle their database with such big data?
Is that a good idea to use one database for 50.000+ shops?
0
votes
2answers
59 views
What to have in the main table and what to have in meta_table?
I'm try to figure out the best scheme for the users table for my software. I'm trying to separate the main data and auxiliary data into 2 table called users and user_meta for the sake of extensibility ...
0
votes
2answers
219 views
Database design of Ledger Account and Balance sheet generation
I am building an application which has deposit and loan accounts management, for which i have to develop ledger accounts and then generate balance sheet. Being new to database design, i am having a ...
1
vote
2answers
162 views
History table design for supertype/subtype
I'm designing an asset management database that tracks IT hardware. I decided to use a supertype/subtype design. I'm at a point where I want to track history of changes for devices. I wanted to use a ...
0
votes
2answers
70 views
looping through a table type variable
I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value.
CREATE OR REPLACE FUNCTION ...
0
votes
1answer
36 views
Create two tables with identical superkeys?
I have an existing table called Realms. This has the superkey rlmID (just a surrogate key). We are planning to add tables that group realms in some context -- let's just calll them RealmGroups. ...
4
votes
1answer
200 views
Best way to index a table with two datetime columns that are equally queried
Sql Server 2008 R2.
I have a table with ~70m records, about 10 inserts per second.
It's currently clustered on a CreatedAt datetime column, which always increases. 50% of queries involve this ...
0
votes
1answer
187 views
How to connect to a Database made by Oracle SQL Database?
So I am fairly new at this, so if you could keep that in mind in your answers, it would be much appreciated. I installed Oracle SQL Database on my Windows PC. It came in two zip files. I installed it ...
1
vote
2answers
399 views
Designing a Products database: One Products table or many separated by vendor?
In order to prevent the unwieldiness of managing product data spread across a number of excel spreadsheets, I have set up a Products database.
My data is in spreadsheets because it was (somewhat) ...
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 ...
2
votes
1answer
63 views
How to design functionality to comment about a user joining a Facebook group?
User A joined the group DBA
The table responsible for users x groups is "Following" table.
user_id group_id
1 1
1 2
2 1
And ...
2
votes
2answers
87 views
What is the way to paginate last updated rows from multiple tables?
I want to have something like a newsfeed from multiple objects sort by most_recent. However I need to paginate (load more/offset) the results.
Row1 = Post's - text, username
Row2 = Photo's - text, ...
2
votes
2answers
152 views
multivalued weak key in ER database modeling
I was wondering since i didnt find out any clarification for this.
I want to store movies that exist in different formats (dvd, bluray etc) and the price for each format differs from each other as ...
1
vote
1answer
176 views
How to handle huge table
We have a problem with a few tables that are very very big slowing down the system. so my question is if that size is normal for the amount of rows in the table, and also what could be done to make it ...
1
vote
1answer
77 views
Storing Different Products and Joining on table Names
I am setting up a database using mysql to store different products with different product types.
For example I could have Books, Software and clothes etc.
What I am think of is creating a parent ...
0
votes
2answers
613 views
Billing Module (Fee) for School Management System
I am working on a School Management software which includes module for billing of students i.e, fee collection from students.
The fee is collected on monthly basis (12 times a year) and the total ...
0
votes
2answers
146 views
Relationships in a school database
I am designing database for a school app. Student is the main entity in the database. Student has relation with Parent & Guardian.
I am confused as where to store references. As will student ...
0
votes
0answers
9 views
speed up data transfer from SQL server to application [duplicate]
I have build an application that handles data stored in a MS SQL server database.
This server is hosted externally of our company and has to be connected to via SSH because of company rules.
On a ...
0
votes
2answers
36 views
creating user related user specific content
Should you create a table for each user to contain information on specific content for an account, say favorite articles, interests, ect. User defined lists that would be used to generate useful ...
2
votes
1answer
98 views
How to model a user/group relationship where users are assigned an unique number within the group?
I am working on a web-server applications that deals with 2 entities:
users
groups
A user can belong to one, several or no group at all. Fairly usual, I assume.
However, I have additional ...
1
vote
1answer
157 views
Payment methods conceptual and logical model
I need to create a conceptual and logical (normalized) models of parking house according to the requirements below. It looks to me as a very simple concept that doesn't need all tables to have ...
2
votes
1answer
85 views
Is there any reason that metadata should not be a part of my database schema?
By metadata I mean things such as:
the owner of an entity
permissions associated with an entity
the date and time of data entry
the surety of entered data
the format of a text attribute
descriptive ...
0
votes
1answer
191 views
Connecting to a SQL Server database from a Flash program
I currently have the ability to utilize Microsoft SQL Server 2012. I am developing a project with Adobe Flash Builder 4.7.
If I link my database with Adobe Flash Builder is there any additional ...
1
vote
1answer
95 views
Setting up simple relational database structure
I'd like to set up simple MySQL database but I'm a bit confused about everything, so let me try to explain...
I have 4 relevant tables, let's call them POS, POS_STRUCT, POS_STRUCT_DIV and MEMBERS.
...
3
votes
1answer
365 views
Very Slow MySQL Queries, even with indexes
I have a relatively large 4-deep relational data setup like this:
client_applications: (potentially 1,000's of records)
- ...
- account_id
- ...
0
votes
1answer
124 views
What are good resources on data modelling for business intelligence? [closed]
We are a SaaS startup offering purchase & procurement solutions. The data model for operational transactions has been created (relational DB). We also want to offer analytics on those transactions ...
0
votes
1answer
48 views
Facebook generates a Post record when I rename a group's name or just an ActivityItem?
My question is more about relational database scheme.
I have in mind two tables for it:
Posts
Activities
Where when I post a a status message it probably creates a record for a Post and an Activity ...
3
votes
3answers
312 views
Is a surrogate key better than a natural key in this case
I copied this code from here:
CREATE TABLE records(
email TEXT REFERENCES users(email),
lat DECIMAL,
lon DECIMAL,
depth TEXT,
upload_date TIMESTAMP,
comment TEXT,
PRIMARY ...
