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.
1
vote
3answers
132 views
How to model inheritance of two tables mysql
I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal).
...
2
votes
3answers
258 views
Simple scenario needs to go from Unnormalized Data to 3NF - Review Please
NOTE: The Question has been updated. View Edit Revisions to view original question.
The scenario. An animal charity that at the moment, track their establishment's costs via a spreadsheet like so:
...
1
vote
1answer
73 views
table design to track significant events associated with an item
An Orders table I'm dealing with at present in design phase has about 10 or more columns.
Each OrderID in the Orders table will have certain significant events or milestones. The number of Orders ...
3
votes
1answer
179 views
Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field
This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
1
vote
1answer
138 views
Possible reasons for slow BLOB updates in Oracle?
We have an application that transfers .bmp images as BLOBs to an Oracle table. One of the users of the app has complained that it is taking 2-3 seconds to transfer each image. Each image is ~1.2Mb. ...
0
votes
1answer
29 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
24 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
0answers
48 views
Designing ERD1 and ERD2 with crows foot [closed]
What would be the perfect ERD1 & ERD2 for the following case study? And what are the attributes of the entities?
Five Aside Football Independent League (FAFIL)
The Five Aside Football ...
0
votes
1answer
93 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
76 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
65 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
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
27 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
1answer
32 views
Refer to a table with all rows or to the table which restricts allowed rows?
There are some discounts:
CREATE TABLE `discounts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`value` decimal(4,2) NOT NULL,
`titleru` text COLLATE utf8_bin NOT NULL,
`titlehe` text ...
3
votes
0answers
101 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
66 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
58 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 ...
1
vote
0answers
70 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
96 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:
...
6
votes
4answers
161 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 ...
3
votes
1answer
88 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?
0
votes
1answer
61 views
Book / Tutorials / Website - Creating Custom C Database [closed]
I want to create the custom database for my project. Kindly recommend me Good Book or Tutorials or Website which provide the detailed information.
The features want to implement are full text ...
1
vote
3answers
167 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
93 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
...
3
votes
2answers
166 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
162 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
53 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
300 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
309 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
90 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 ...
-1
votes
2answers
110 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
97 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
359 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
90 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
155 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
41 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
98 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:
...
3
votes
2answers
85 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 ...
6
votes
3answers
523 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
90 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. ...
3
votes
2answers
57 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
87 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
62 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
87 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
107 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
77 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
0answers
135 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
79 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 ...
3
votes
1answer
91 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 ...
3
votes
1answer
87 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 ...
