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)

2
votes
1answer
1k views

Normalisation from 1NF to 3NF

I need some guidance on normalisation from 1st Normal Form to 3rd Normal form. I have already set up my database which is working fine but needed to show some normalisation to show that I did do some ...
2
votes
1answer
588 views

How do I get ER Studio Data Architect to automatically add SCD Fields to my Dimensions?

This is my first try using this tool and Google searches have been fruitless. How can I get ER Studio Data Architect to automatically add the SCD Maintenance Attributes (fields) to the tables I ...
2
votes
1answer
679 views

Database Design Cars-Models / Car Classifieds

Im developing a Car Classifieds but im not sure of how is the best way to design something, there are some features of cars like if it have 2,4 doors, if it is 4x4 or 4x2, engine 1.6, 2.0. All this ...
1
vote
1answer
62 views

Correct data model for users with clients

I have an application where users are granted access. Part of the system allows users to have their own 'clients'. I have a users table ******************************** users ...
1
vote
1answer
180 views

Multiple primary keys

I am new to database design and have been looking at some example data models here. One thing I don't understand is if you have a table like client_addresses from the link above: ...
2
votes
2answers
422 views

How to design future proof tables?

I am new to database design and have started developing my first schema. Some of my tables (e.g. user_profile) I am struggling to come up with an exhaustive list of fields for the table. A particular ...
8
votes
3answers
1k views

SQL Database Structure for RESTful API

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources. Initially, I though a table per resource would be a good way to go, but I'm ...
2
votes
1answer
2k views

Storing IPv4 and IPv6 hosts and networks

I have to store the IPv4/v6 address and networks(CIDR notation) in a MySQL database. Unfortunately, I am constrained to use Mysql. So, I'm looking for the equivalent of the inet type of PostgreSql. ...
4
votes
2answers
109 views

Which database type to use

I want to implement database for following application:- User creates task and assigns to other user who can furthur split it into subtasks and assign it to other users.creaking a tree like ...
0
votes
1answer
410 views

Same product name with different price in different states

Consider the price of products in the table differs in each state. If the price varies in district I also have to mention it. How can I do this for more than 5 states? product Name Price ...
2
votes
2answers
404 views

Query design for tracking order status

I have the following tables in MySQL `orders` ( `orderID` int(10) unsigned NOT NULL auto_increment, `agentID` int(10) unsigned NOT NULL, `order_date` datetime NOT NULL, `senderID` int(10) ...
3
votes
1answer
94 views

What to do with duplicate lookup information

I have multiple databases that I want to store in one data warehouse database. I am wondering how I design the import process to handle multiple lookup tables. For example, say I have 5 databases all ...
1
vote
4answers
184 views

Normalization of DB

I have designed a database using MySQL likewise My question is what else i can do to normalize the DB, for now i am using InnoDB engine for the table to maintain the relation among different tables. ...
0
votes
2answers
108 views

How to design data about a column can belong to the whole table?

I'm not a DBA so I apologize for lack of knowledge. Here's my situation...Say I have several tables. Products, Customers, Vendors, etc. Our application may have a help text for some of the data in ...
2
votes
1answer
643 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 ...
1
vote
1answer
361 views

Conceptual Design of a data warehouse (declaring the grain)

I am trying to design a data warehouse using this example scrubbed data. I need to declare the "grain". I know its the lowest level of detail in the fact table, but I am having a hard time determining ...
4
votes
1answer
205 views

Symmetric self-referential many-to-many

I'm trying to model a hierarchy of categories where a category can have multiple parents ( an overlapping tree model as described in this book ) I have the following tables video_categories int ...
4
votes
1answer
1k views

What relationship type to use for this messaging system?

I am trying to make a small messaging system. It should have these properties: One user can send a message to one or many users Messages can be sent only to registered users (i.e. to an existing ...
7
votes
1answer
453 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 ...
4
votes
1answer
502 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 ...
10
votes
3answers
5k 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
192 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
168 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. ...
3
votes
1answer
231 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 ...
5
votes
1answer
3k 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
800 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 ...
7
votes
3answers
2k 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
5k 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
159 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
3k 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 ...
29
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
101 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
5answers
9k views

Coding an accounting database from scratch? [closed]

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
5k 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
118 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
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
581 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
973 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
798 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
738 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
151 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
45 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
433 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
6k 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
345 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
210 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
143 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
496 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 ...