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
5answers
96 views

Database with only text fields

I started working on an existing SQL Server Database System in which most of the fields are stored as text. Except some IDs all fields are varchar (phone numbers, zip codes, Dates, addresses, monetary ...
10
votes
5answers
1k views

Why does a UNIQUE constraint allow only one NULL?

Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of ...
0
votes
1answer
229 views

How to model medium.com paragraph comment

If you have used medium.com, I am interested to know more about how to model readers' paragraph comments in database. Specifically, how connections between these comments and paragraphs are modeled? ...
2
votes
4answers
111 views

Can't finish my ER diagram

I am trying to create a database for a small book library. This is not a professional project, just an exercise of my own. This is my first time to try this ( I am self-taught ) and I am encountering ...
0
votes
0answers
33 views

DB Model for Small requiremnt [closed]

I have created below DB model. Could you please validate if this is proper. This is restaurant app where waiter takes order and gives it to chef.Before placing order she does have information of ...
-2
votes
1answer
36 views

Oracle Concurrent Insertion

We have simple case like Create table t1 (c1 number primary); session1: insert into t1 values (1); //session1 do not commit transaction session2: insert into t1 values (2); Will Session2 ...
1
vote
3answers
35 views

Study material for Advanced Databases module inc SQL, Relational Model, ERM & TM [closed]

For my next module in my masters, I am planning on studying Advanced Databases. (Long story short, the masters is designed for people with NON-CS degrees, so I have never studied databases, therefore ...
0
votes
0answers
125 views

Suitable database to store GPS track data

I have a network of vehicles. Each vehicle is equipped with a GPS sensor sending data over 3G to my FTP server. As a first solution to test the sensors, each vehicle sensor sends a .csv file to the ...
1
vote
1answer
58 views
5
votes
2answers
187 views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who ...
0
votes
1answer
67 views

A question regarding the performance of normalization

I am sorry this is such a newb question, but it is one thing I am having a difficult time wrapping my mind around, and after an evening spent reading I finally just decided to ask for some ...
1
vote
1answer
42 views

Question about the workflow/design process/tools used by professional database designers

I hope this in the right place, as it's really more a question about database design software than the philosophy and practise of database administration. I'm a masters student doing an "introduction ...
1
vote
0answers
37 views

triggers vs views [closed]

I have a mysql database with the following tables Penalties Scores player_instance When penalties is updated/deleted then it will update scores (via a trigger). Also when a player-instance row is ...
0
votes
1answer
34 views

Should there be a temporary table to hold Order and OrderItems? [closed]

After trying out a few open-source restaurant POS, I still couldn't decide if there should be a temporary to hold Orders and OrderItems? I was initially thinking to have temporary tables and ...
1
vote
2answers
60 views

Dimension or Degenerate Dimension?

I am taking a course on data warehousing. Currently, I am reading through Ralph Kimball's Data Warehouse Toolkit. I understand that a degenerate dimension is a dimension key that is stored in a fact ...
1
vote
1answer
46 views

Saas MySQL Multiple Databases or One Database [closed]

I am using MYSQL so Database is the Schema, sorry if not using right terminology. I currently have a Saas product which has multiple databases one for each client with a main databases which we ...
0
votes
1answer
27 views

Does the creation of constraints affect the views that have been created earlier?

Does the creation of constraints(foreign key) affect the views that we have created earlier? create view allreservations (fno, customer) as SELECT f.flight_number, customer_number FROM flight f, ...
2
votes
0answers
81 views

CouchDB and document versioning

I'm currently working on a wiki-esque application using CouchDB and am trying to implement a document versioning scheme. The way I see it there are two ways of doing this: Store each version as a ...
0
votes
2answers
39 views

about giving same column name in every table of database

I have a database with 5 tables. In every table there is a column for checking status. If i give the name to that column which is same in all the 5 tables; What will happen? Is it correct according ...
0
votes
0answers
21 views

Database & Structure for SaaS reporting software

I'm building a SaaS application to allow users to upload data and perform reporting and analytics on it. The reporting and analytics would be quite simple like grouping and aggregating. This has ...
0
votes
0answers
96 views

How to select column names that contains specific value from other table's column

Suppose I have table items and logs. Both tables have col1, col2, col3, col4... col(n). col1.. col(n) in table items simply specifies key name (col1 = "beef", col2 = "chicken", col3 = "bacon") ...
0
votes
1answer
37 views

Postgres table contains 1 column, and that column is PK. How to insert new record?

In my application, I have an abstract base class, and 2 derived classes. The number of derived classed will probably not increase. If it does, it won't be more than a few. The class structure looks ...
0
votes
1answer
52 views

ERD Design for Employee Leave

I found the following ERD design: Which trying to capture as much information about employee leave (i.e sick, travel, etc). What am trying to understand is what does the columns: Table : ...
0
votes
1answer
54 views

ORM for Image model

I'm uncertain as to how a certain table should establish relationships, and I feel as if the solution I came up with is incorrect. The images table is used to store an absolute path on a server ...
1
vote
1answer
50 views

Tables names in a supertype/subtype schema

I have 3 types of users in my app. The users share some common attributes, but they also have quite a bit of stuff that pertains uniquely to each user type. I have a person table that contains limited ...
1
vote
1answer
15 views

Schema Design Inquiry

In a timesheets data model, suppose I have an employee table, and a job table. Then, I also have an employeeInJob table to store an employee's rate for a given job e.g. employeeInJob ( ...
0
votes
0answers
24 views

Two types of rows with mutual fields and unique fields

I have a question about DB design. (MySQL) In my website, users can leave reviews for Stores and Products. Right now, there's a table for stores and a table for products. Each item ('item' = either ...
0
votes
1answer
56 views

How do I organize tables for simple lottery game? mysql [closed]

I am building a simple lottery web game that will let users pick a number that is in between that weeks given range. A number will be randomly select to determine the winner. I have a table for users ...
0
votes
2answers
38 views

Design a Lab Report tables [closed]

I need to design tables for a laboratory test. I will have Lab test name, and its component to be saved. The component can be changed with time. I need to design a UI for update. Also each ...
1
vote
1answer
38 views

Database setup for datalogging

We're currently starting to build a version 2 of our system and one of the things we've noticed with version 1 is that we'll have quite a bit of data so we started to rethink our approach to how to ...
0
votes
2answers
49 views

How to configure Standby database in oracle for read/write

I have configured a physical standby database in Oracle 11gR2, and the standby database it's working fine. All the changes in the principal database are replicating to the standby database, the ...
0
votes
0answers
19 views

Merge colums from two different databases using phpMyAdmin

Is there a way to: Replace all the data from database1 > table1 > column2 With Data from Database2 > table1 > column2 ONLY under the condition that the ID value in database1 > table1 > column1 ...
1
vote
0answers
62 views

How to represent directed graph with multiple parents?

http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html provides an algorithm for inserting and deleting from a Closure Table. I'd like to model a similar data structure, except that ...
7
votes
5answers
28k views

Matching single column against multiple values without self-joining table in MySQL

We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data: user_id ...
1
vote
2answers
77 views

Unique column values between tables

I'm keeping track of teams and their members. Each team has one leader, one contact person, and any number of players, and each of them has a unique email address. The problem is that one physical ...
3
votes
2answers
53 views

I have this two models, which one is better?

what is a better design?? Model 1: +------+ +--------+ | type |-|--+ | Data | +------+ | +--------+ | id | | | Mach ID| | type | | | Time | | len | ...
5
votes
3answers
3k views

How costly is opening and closing of a DB connection?

How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL ... when the DB software is on localhost? ... when the DB software is on another machine?
2
votes
1answer
53 views

Supertyping Inventory Transactions

I am trying to wrap all interactions with inventory items into one transactional table. Currently I calculate QoH by querying multiple fact tables (production, acquisition, shipment, return order, and ...
0
votes
3answers
43 views

MYSQL Indexes when looking up on alternating sets of columns

I have a table that looks like: CREATE TABLE `connections` ( `connection_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `service_id` smallint(5) unsigned DEFAULT NULL, `parent_id` bigint(20) ...
1
vote
1answer
245 views

Database design for an online babyShop ,Toy shop

Hi i'm designing a database for a toy shop this shop offer many products with different categories. boystoys,girlstoys,puzzles,beds,stroller, etc i designed my database as it is consists of 5 tables ...
0
votes
1answer
29 views

Is it possible to create this type of database for a massive forum? [closed]

Kind of like how craigslist you select a state, then a city, and you're in that community. How do I make my forum website's home page have 2 drop menus for a state and city, then it would bring the ...
0
votes
0answers
14 views

Handle a Search on 2 Big Tables

Hello i want to realize a exchange system with 2 tables. 1 Table for "the user is looking for" 1 Table for "the user has to share" the Tables are very simple id bigint user_id bigint object_id ...
0
votes
1answer
94 views

Database design : hotel room and its detail

Right now i'm creating DB schema for a hotel. I stumbled upon on my hotel rooms table design. The requirement of hotel rooms would have data like : columns like room_type, room_size, room_number. ...
0
votes
2answers
129 views

Database design: same products in inventory but with different cost

I am trying to design a database for a Stock and Sale System and I have the following issue. Suppose there is an item in stock that costs $ 100 (product cost, not the selling price), but there are ...
1
vote
1answer
28 views

different log file for master and beta mysql

Hi i am facing a problem while shipping my database log I have three database server dev, beta and master and i maintain log files for dev on github and every log file has a version given to it like ...
0
votes
0answers
8 views

CROSS JOIN and updatable model design for Ruby on Rails [duplicate]

I am trying to build the multi-level model in rub on rails to allow the following to happen: When a user creates a campaign they will select some categories and some metros from tables Category and ...
0
votes
0answers
40 views

Multi-Tiered Database Design Issue

I am trying to build the multi-level model as demonstrated. When a user creates a campaign they will select some categories and some metros from tables Category and Metro. Those selections will then ...
0
votes
1answer
16 views

updatable entities in the design of databases

I have a question on Entity Relationship design. It is a shop where products are kept in stock first and then go to the store. When the store has less than a certain amount of product, products that ...
0
votes
1answer
34 views

Schema for a reservation system

I'm working on a reservation system schema and have some problems defining availability of resources. I searched for similar question here and on Google but still don't have clear understanding My ...
0
votes
2answers
63 views

Best Way to Migrate 100 tables To New Database

I need to migrate 100 out of 150 tables (data and structure) and everything else (sprocs, views etc.) to a new empty SQL Server database. I have been using the Import/Export wizard and it has been ...