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)

0
votes
2answers
3k 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
559 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 ...
7
votes
1answer
913 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
76 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
775 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
709 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
150 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
43 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. ...
5
votes
1answer
415 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 ...
1
vote
3answers
5k 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
334 views

PeopleSoft or Oracle App Designer Tool Alternative [closed]

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
209 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
141 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, ...
3
votes
2answers
483 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
1k 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
287 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
2k 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
455 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
54 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
801 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 ...
3
votes
1answer
722 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
118 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 ...
1
vote
1answer
801 views

Connecting to a SQL Server database from a Flash program [closed]

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
156 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
2k 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
279 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
60 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 ...
4
votes
3answers
689 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 ...
3
votes
3answers
2k views

Is it a good practice to apply Vertical Partition to a newly created model?

Suppose you are modeling the ER model for a new Object Oriented Web Application. There is a table with 20 columns, and there are going to be a lot of INSERTs populating 10 columns, and a lot of ...
0
votes
1answer
278 views

Maintain table with too many insert

I have to create a table. Its size is going to increase very fast. Is there going to be any issue if many insert queries are fired at the same instant as mentioned here - User autoincrement ID for ...
1
vote
2answers
485 views

Database Design - One table from many source(One to One)

I have a 'Text' table, the text table may came from different source, like Tweet, SMS, Email, etc. and each source have their own log, and Text is came from the source. so i came with some solution, ...
9
votes
1answer
5k views

How to manage 3.1 billion rows of data?

I am currently tasked with implementing a storage schema for a relatively large amount of data. The data will primarily be accessed to determine a current data point value, but I am also required to ...
0
votes
1answer
275 views

Is this schema usable for multi-tenant clients?How to remove multi-cascade paths?

I am newbie to database design. I want to build multi-tenant application that will have the following schema for its data: School(*schoolid*, schoolname, schooltype); ...
2
votes
2answers
763 views

Separate or Combine 2 Tables with Similar Attributes?

I have two kind of users in my business system: Customer and Employee. Both user have Username, Password, Fullname, Phone Number, Email, and other similar attributes. I have a difficulty to ...
3
votes
1answer
59 views

Model to handle music release artwork

I’m designing a FAN web page for one artist, more specifically, the discography section. The discography includes this entire artist CD’s, Vinyls, 8-tracks, cassette, etc. So far, I’ve build something ...
3
votes
2answers
137 views

Rebuilding system and tables, should I change primary key to int?

I am in the process of rebuilding a current system (orders, invoicing, payments, Vendors, etc), which includes normalizing the current tables with more tables and lookup tables to spead up ...
3
votes
1answer
455 views

How to design sales model for escrow type payment process

I need advice for best sales model for payment process. Basically my system is kind of market place. There is two kind of transaction. Customer will pay full money to administrator. Administrator ...
2
votes
1answer
5k views

Decomposition of a relation to 2NF then to 3NF

I'm currently studying for my exams and the trouble I am having is how to decompose a relation R with given functional dependencies into 2NF then 3NF. For example for the following R and functional ...
1
vote
3answers
398 views

Design for archiving a sorted list (with ability for a few INSERT)

I have a somewhat static sorted list of words which I want to save in a database table: -- simplified! -- Note: the rank/order of each item is NOT calculable from other values! CREATE table words ( ...
6
votes
2answers
741 views

Extracting entity from attribute in entity-relationship diagram

I am developing a database model where I need to capture information about rent contracts for parking places within a parking. I have thus defined an entity called PARKING CONTRACT. Requirements (r.) ...
2
votes
2answers
204 views

What's the best practice for representing set operations in a relational database?

I have a simple database representing collections of entities and sets of those collections. Sets contain multiple collections and collections contain multiple entities. Currently, when I query all ...
3
votes
1answer
232 views

What is the right way to manage users data?

I'm trying to create a simple register login script with user profiles, I created a table with the following structure: +------------+---------------+------+-----+---------+----------------+ | Field ...
6
votes
2answers
279 views

How many fields is too much for a primary key?

I am creating a table to store information about the placement of objects in a grid (game map). For example, I have a 10x10 grid and I want to store, in the database, that cell (5,5) has a blue box, ...
2
votes
1answer
150 views

Databases design decision - Mobile & Web

This may be a question a little broad but... I'm currently having the following dilemma: Having a web application (already deployed) and a new mobile app, I'm currently working on the mobile app ...
1
vote
2answers
249 views

XML Large DataBase is the best? [closed]

Is the best way to implement a large database is XML? Or is there a better way? 1 - reduce the time complexity 2 - Reduce the amount of traffic between the tables And other factors ... Which ...
1
vote
1answer
111 views

Issues with a Table having 120 fields in mysql

I am creating a table with 120 fields in MySQL. It will have 50 varchar(255) fields, other are maximum fields with char(1) , 1 date field and 5 double fields and others are int(11) fields. My ...
0
votes
1answer
346 views

Design database to query a date range for scheduling

I am designing a db that should include a table with fields for datetime scheduling, so as I know what kind of queries will be made​​ periodically I would like to design the table for them to be as ...
5
votes
3answers
2k views

Partition pruning with multiple date columns

I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are ...
2
votes
1answer
387 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 ...