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.
2
votes
1answer
50 views
How to enforce a nullable foreign key?
I have a relation between two tables. The foreign key table can have a row related in the primary table. There's a way to enforce the value of the FK column to be NULL or one of the values of the PK ...
0
votes
1answer
19 views
Which of tables key to reference?
MySQL.
There is a table users with autoincrement primary key id.
There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid ...
2
votes
1answer
29 views
Relational Database tables
I have a table Player where playerID is the primary key. Then I have a table Game where gameDate is the primary key (of type DATETIME). Each game is played by 2 players and each player can play many ...
0
votes
1answer
37 views
One to at most one relation or multiple null columns?
I have a table with many attributes that can be grouped in logical groups, and thus the idea of put this attributes in separate tables seems attractive. The logical groups doesn't represent entities ...
0
votes
1answer
44 views
Is it better to use multiple one-to-many relationships, or polymorphic?
I've got the following tables:
Clicks table
Websites table
Ads table
Both Ads and Websites have many Clicks (i.e. both models have a one-to-many relation with Clicks). In such cases, is it better ...
0
votes
2answers
71 views
How to snapshot or version a relational database when data changes?
My system receives data feeds. Each data feed will end up creating inserts and/or updates to most tables in the (relational) database.
I need to capture the snapshot of what the entire database ...
2
votes
2answers
183 views
Storing data as rows as opposed to columns
Here's a typical way I would store data (obviously not storing the password in plain text)
USER TABLE
| UserID | UserName | FullName | EMail | Password |
...
7
votes
4answers
493 views
How to create a database for unknown kinds of data?
I am in the process of designing a database for a new PHP/MySql-based application.
My problem is that I do not and cannot represent what should be saved in the database because it is unlimited and ...
1
vote
2answers
67 views
How should I store multiple relationships (many posts to many categories)?
I have posts stored in a posts table and I want each post to have multiple categories, what is the most efficient way to store the categories and their relationships ?
I thought of it this way first:
...
1
vote
1answer
48 views
Database design: nested tagging
I have a situation that after an initial thought ended in the following tables:
section: id, name
section_tag: id, name
section_tag_map: section_id, tag_id
item: id, name
...
1
vote
3answers
107 views
Help with modeling this table
Thanks for reading my post...I'm new at setting up databases and am working on a simple practice app.
I have a table called PhoneNumbers, which hold a list of phone numbers (duh). I have two other ...
1
vote
3answers
202 views
Relational vs Non-Relational Database for Events Database
I'm trying to find out whether an SQL or no-SQL solution would be better for creating an events database. I'm creating a ticketing system, similar to ticket master. I know that for either ...
3
votes
2answers
174 views
Why use separate databases for high traffic/volume tables?
While looking at the database structure of an application I am using I recognized that it uses 3 different databases on the same SQL Server instance for different things.
The first one contains the ...
0
votes
0answers
27 views
In regards to GS1 barcode - what size DB field are used?
In regards to GS1 barcode - what size DB field is used or is the entire GS1 typically not stored in database, but rather processed in the application layer and stored in the database as a collection ...
1
vote
4answers
105 views
Unsure how to implement certain constraints and relations in this relational database schema
I am in the middle of designing the dB for school project- airline ticketing database.(Using MySQL workbench)
So far, I came up with the following design:
Here are couple things I can't figure out: ...
2
votes
2answers
60 views
Should I create an enumeration or individual flags?
I'm creating a new database table which will be used to store pictures of products. I'd like to identify the product "type" represented in the picture. Say we have 3 general categories for all the ...
1
vote
2answers
106 views
Unique Constraint with multiple null columns
I have two tables
PC(Id,EmpName Not NULL, PCName NULL, HostName NULL,.... PhysicalLocation NULL, PCType Not NULL)
PCNetwork(Id, EmpName Not NULL, PCName NULL, HostName ...
1
vote
1answer
61 views
Referencing multiple tables from a single table and store additional data
I have a database where I want to store lap time information from a racing game. I have tables for car, track, game, tuning and upgrade data and want to put all these together to create a single ...
0
votes
1answer
70 views
Database schema design help needed
I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance.
This application ...
1
vote
1answer
81 views
Approach for a 2,000 table migration
What might be the best approach when converting or migrating a database containing over 2,000 tables? Where would you begin to tackle such an issue? Steps to take in the first weeks of designing? ...
1
vote
2answers
71 views
database design- diagrams
I'm trying to derive an Entity Relation diagram from a class diagram.
In the class diagram, I have a class Player with one to many relationship to another class Payment. In Payment there is no ...
7
votes
5answers
635 views
Database design: Dividing multiple identical tables, good or bad?
I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them.
I have seats at a theater, the seats are divided into 4 main areas ...
2
votes
1answer
80 views
Db design for historical option data (~200GB)
I need some help determining how to best design my tables. I have about 200GB worth of data I wish to place in a table (historical option price data). The table looks something like:
String symbol
...
1
vote
1answer
50 views
Game database- many to many relationship
I am designing a simple relational database about a game that can be played by 2 players each
time. I have these tables: Player, Game, PlayerPlaysGame, Payment. I recognize there is a
...
0
votes
0answers
45 views
How to identify computer specifications [closed]
We need to assemble a system that can store streaming data from a dozen sensors, including several cameras, averaging >5 Gb / minute. The desired data storage system is a single machine running a DBMS ...
12
votes
11answers
844 views
Why are constraints applied in the database rather than the code?
Why are constraint applied in Database? Will it not be more flexible to be in code?
I'm reading a beginners book on implementing databases, so this is a beginner's question!
Let's say I design a ...
2
votes
2answers
103 views
How to implement posts with “seen by” like facebook?
Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me.
And if I see a specific post inside a group thought a url ...
-1
votes
2answers
56 views
Column or new table [closed]
I am working on a student management system for a private school. In the table "student" I need to indicate which public school the student came from. Can I just keep this information as a column ...
0
votes
2answers
105 views
“Variant” values structure and their use for multiple columns
note to the edit: the question originally was only about the opportunity of using a single "variant" structure for multiple columns, but all initial comments asked clarifications about the actual ...
4
votes
1answer
120 views
How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?
According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause
some ...
1
vote
3answers
88 views
How to handle update or insert situations
From time to time my stored procedures looks like
create procedure handle_data
@fk int
,@value varchar(10)
as
begin
if exists (select * from my_table ...
3
votes
1answer
117 views
Could too many idle connections affect PostgreSQL 9.2 performance?
Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
1
vote
2answers
122 views
Multiple SQL Server data files on same SAN disk
I'm currently in the process of creating a new database, and have previously only ever used a single data file and a single log file. I've done some research online regarding the benefits of multiple ...
0
votes
3answers
57 views
Column partially determining accepted values of another column, should be somehow normalized?
I have a table that describes a set of properties (yes it's metadata, in this case there's a valid reason to have it in the database); among other things I tell the type of the data that can be ...
4
votes
3answers
339 views
Should I seperate frequently updated columns
I have a users table that contains users' information and a column named credits that is frequently updated.
On index page I'm showing list of users with basic user information but I don't need ...
0
votes
1answer
93 views
Trying to create a Data Model
It may sound stupid but it's my first experience ever with databases.
It's a simple database for a car accessories store. I was hoping when making a sale on a certain item it effects the quantity of ...
1
vote
1answer
73 views
Unique index for subquery with RANK() function
I have the following tables:
CREATE TABLE Revision (
RevisionId INT PRIMARY KEY IDENTITY,
UserName NVARCHAR(256) NOT NULL,
DateTime DATETIME NOT NULL DEFAULT GETDATE()
)
CREATE TABLE ...
1
vote
1answer
33 views
Adding users to a list and sort by category?
I'm using SQL-Server 2012 and I would like to know how to solve this program.
So:
I want to create a page that allows admins to manage a project, and part of it, would be to add members/employees to ...
3
votes
4answers
723 views
How to Handle TimeZone Properly in SQL SERVER?
I have some issue which needed to be fixed quickly. My local development server is in middle east. But my production server is in UK. Now, I need to show the date to user to thier timezone. For ...
0
votes
1answer
72 views
good practices of creating tables in mysql [closed]
Hi I was thinking of developing an web apllication using php and mysql.
Application will give a professor the required data about the student.
i want to design application considering Engineering ...
-1
votes
1answer
42 views
Research studies on electronic vs paper record keeping accuracy [closed]
Does anyone know of any research studies to test whether electronic or paper record keeping is more accurate than the other? Obviously electronic record keeping is more efficient, but efficient ...
-1
votes
1answer
47 views
Database theories possible topics to research through [closed]
I need to conduct a research on database theories. I want to know what possible questions should I research on which is believed to be suitable when researching database topics for a online assessment ...
1
vote
2answers
67 views
Handling expiration in a course registration or ticketing system
I want to restructure the database for a web application I inherited for handling summer camp registrations, but my question is relevant to any event registration or ticketing system.
My question is ...
2
votes
0answers
65 views
How to execute a non-table-locking update operation on PostgreSQL? [closed]
Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
4
votes
2answers
110 views
Foreign keys - link using surrogate or natural key?
Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack ...
0
votes
1answer
70 views
Foreign Keys with Composite Key
Imagine I have a table with the following dependency:
(Name, BoughtFrom, TimeBought) --> Cost
Where the three attributes in bold form a composite primary key for the table. Now then, I want to ...
2
votes
1answer
207 views
Bitmask Flags with Lookup Tables Clarification
I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
1
vote
0answers
59 views
Real Time data collection - Help select Database
My Scenario:
I want to collect data from various sensors[for now I have 600 sensors sending data, but this may have to scale up to 3000 more data sensors, all sensors send real time data] Now I have ...
0
votes
1answer
62 views
List partitioning or filtering a non-partitioned table, which incurs lower cost?
Consider a list partitioned table PARTITIONED__SOURCE_TBL on column period, and non-partitioned table SOURCE_TBL also having the same columns. Assume all tables are adequately indexed.
Set 1:
...
1
vote
2answers
132 views
How do I design a database for a resource scheduling and allocation application?
I have two entities: Resource and Project. A Project runs for a certain amount of time (in calendar days), and I need to allocate several Resources to it (allocation interval - 1 work day, not an ...
