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
1answer
33 views

mySQL Quickly find rows linked to another row

For example if I have a posts table with id and a likes table width id post_id user_id, how can I set up the database to organize the likes table by post_id in order so queries are faster? I can't ...
0
votes
1answer
27 views

Automatically create linked row in a different table

What I mean is that for example if I have a table user with id name prefId, and a second table preferences with id etc..., is it possible that when I add a row to user, it would automatically create a ...
0
votes
1answer
530 views

Efficient way to move rows across the tables?

This is somewhat long question as I would like to explain all details of the problem. System Description We have a queue of incoming messages from external system(s). Messages are immediately stored ...
2
votes
2answers
308 views

Relational database for address model

I want to design an "Address" model for all types of entities like users, businesses, etc. I have two types of main models: one is User and the other is Business. Each one has different address types ...
2
votes
1answer
311 views

SQL Uniqueness Across Multiple Tables

I have two SQL tables: Users and clients. Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field. The users ...
0
votes
0answers
440 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 ...
3
votes
0answers
150 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 ...
4
votes
1answer
152 views

Adding an ID column to a join table (many-to-many) so that it can be targeted

I'm building a web application for a company that sells cars. They buy leads (personal details of people interested in buying cars) and try to turn them into customers. The company has a number of ...
0
votes
1answer
90 views

Implementation of stack in MySQL

I had an abstract idea of implementing a stack in MySQL. Tried a lot of Google work but couldn't reach to any logical conclusion. The Stack must be a collection of tables with the stack top pointing ...
0
votes
0answers
118 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 ...
0
votes
0answers
287 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: ...
8
votes
4answers
664 views

Database Design - different objects with shared tagging

My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here. I'm trying to figure out the best way to design the ...
4
votes
1answer
98 views

Should I always make an attribute id that's an primary key?

I was thinking, sometimes several columns in a table uniquely specify the whole row. In that case, is there a need to create a special column for a primary key? Is that good for something?
1
vote
3answers
2k views

Best practices for history/temporal tables?

Suppose I have an object, with certain fields which I want to track history, and certain fields which I do not want to track history. From a normalization perspective, is the following schema alright: ...
1
vote
2answers
170 views

Suggestion for storing a lot of simple data

I'm looking to crawl a lot of webpages (500,000,000,000) records and be able to store the linking structure for a later date. The way I planned on laying out the database was as follows: 2 Tables ...
5
votes
2answers
869 views

What are the advantages of a database design with single table for MtM relationships?

The database that backs our software product has a table design like the following: Positions ID Name Parts 1 One 12345 2 Two 12346 3 Three 12347 Collections ID ...
2
votes
2answers
2k views

Should I create a separate user table for different web products within the same platform?

We have a web product for young professional that gives them the possibility to create their page to show their professional identity. So a table users that has both information about the user (email, ...
2
votes
1answer
76 views

System to track what Zip Codes my vendors service

I am designing a table configuration that will contain all of the zip codes in the United States. I have a .csv file of all the zip codes in the U.S., so thats at least a start. This will end up as a ...
1
vote
2answers
4k views

Multiple parents and multiple children in product categories

I am making a ecommerce site. In this site I want to categorise the items into three different layers primary category sub category sub category >>electronic ...
1
vote
1answer
756 views

ER Diagram translation to tables

I am trying to translate the ISA relationship in this ER diagram to database tables, and I am not sure how to enforce the total and disjoint participation constraint of Laptop and Desktop in Computer. ...
1
vote
1answer
881 views

Storing metadata of various data types in a MySQL database

I am currently designing a MySQL database that will store a variety of documents in different digital formats (mostly docs, spreadsheets, and pdfs). Each document will have several metadata values ...
0
votes
2answers
1k views

Does MYSQL join before LIMIT?

Imagine that I want to populate the last 10 questions of stackoverflow. I have a table that keep the questions list, a table to keep the question info, a table to list question's asker. Now imagine ...
0
votes
2answers
197 views

ERD Review Please

I am building a simple database driven web application, but from reading so many books on the subject, I know that if my database is messed up, then my application too, will most likely be messed up. ...
2
votes
1answer
996 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
443 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
476 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
56 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
148 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
305 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 ...
7
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
1k 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
99 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
252 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
261 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
160 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
103 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
514 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
197 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
172 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
680 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
328 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
338 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 ...
7
votes
3answers
4k 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
186 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
151 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
177 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
2k 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
557 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
1k 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 ...