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.
3
votes
2answers
90 views
Exclude dataset from a query
I have 2 tables
EXAM (StudentID, SubjectID, SchoolYear, Period, Mark) and
CANCELED_EXAM (StudentID, SubjectID, SchoolYear, Period).
Primary key for both tables are (StudentID, SubjectID, ...
3
votes
1answer
107 views
What is the right way to manage users data?
I'm trying to create a simple register login script with user profiles, I created a table with the following structure:
+------------+---------------+------+-----+---------+----------------+
| Field ...
3
votes
1answer
915 views
Product Attribute List Design Pattern
I am working on updating our website's product database. It’s built in MySQL but this is more of a general database design pattern question.
I’m planning on switching to a Supertype/Subtype pattern. ...
3
votes
3answers
116 views
Is this database fine for calculation?
I am doing an application called as Invoice Application.For that I have my database is like this.
Now my problem comes when I am taking the tax part in my database.As I have made tax as an option ...
3
votes
2answers
304 views
Recommendations for building a big search engine
I am creating a image search engine that would probably have millions of images tagged with keywords. Basically it would be something like Google Image search.
So I want to ask for any tips on ...
3
votes
1answer
102 views
New table or new column
Apologies in advance if this is a dumb question.
Background:
I am a student intern, I've been given the task of adding a new feature the companies client administration system. Basically it involves ...
3
votes
2answers
221 views
Maintaining referential integrity in a booking system
I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005.
I have 3 tables (simplified for this question).
Table 1- CourseSize table- this determines the ...
3
votes
2answers
496 views
Ready-to-Use Database models example
Where can I find ready-to-use database models ?
I don't need a database with data in it, but only schemas (UML diagrams). Perhaps something like the data models at this link, but much more complex ...
3
votes
2answers
115 views
What would be the best way to model my simple table?
What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4?
Or, searching for the right row through a table with only 1 column with ...
3
votes
3answers
72 views
pros/cons of different ways to store whether a record is one of two options?
I am trying to store whether an address is a Work address or a Home address. There will never be another type of address.
I'm wondering what the pros/cons are of the different ways to store this, ...
3
votes
2answers
173 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 ...
3
votes
4answers
717 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 ...
3
votes
1answer
88 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 ...
3
votes
2answers
184 views
How can I organize my tables?
My database contains more than 200 tables. When I want to modify or insert a table, I find it difficult to find it among all those tables.
Is there any way to collect related tables in a folder ...
3
votes
1answer
2k views
What happens when you modify (reduce) a column's length?
Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and ...
3
votes
2answers
184 views
Both Primary and Foreign Key Needed on Table?
Someone recently asked this question:
There are two tables: Deal and DealCategories. One deal can have many
deal categories.
So the proper way should be to make a table called ...
3
votes
3answers
114 views
Optimisation Newbie: How much of a 'sin' is redundancy?
I've been developing for a while, but never really had to deal with DB/scaling issues before. That's suddenly changed and I've found myself in the deep end.
I have 2 SQL tables, as such:
VOTES
...
3
votes
2answers
318 views
Database Modelling of Type, Subtype Relation
I'm trying to model the type and subtype relationship described here.
The best approach I could come up with would look like this:
CREATE TABLE Card_Types (
card_type varchar(12) PRIMARY KEY
...
3
votes
3answers
414 views
Should I use MongoDB or a NoSQL solution at all for this?
I apologize if this belongs in web applications or something.
I'm creating a web application that is sort of a similar concept to github. Basically I plan to allow someone to create a design of ...
3
votes
2answers
123 views
Support ticket system - when/how to move old tickets?
I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting ...
3
votes
2answers
226 views
Save history editable data RDBMS
I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
3
votes
1answer
144 views
Model with variable number of “properties” of different types.
I would like to have a table of microscope slides, where on top of the basic id, name,date_created would each slide also have a number of editable "properties".
Imagine one slide having
...
3
votes
2answers
278 views
Help with a multi-language medical database
My project is to design a database for medicines in many countries.
Medicines have various criteria like composition, price, manufacturer.
Database will be multi-language; related to each country.
...
3
votes
1answer
401 views
Proper tuning for 30GB InnoDB table on server with 48GB RAM
I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
3
votes
2answers
265 views
What exactly is “pinning” in relation to indexes?
When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions.
It is part of a test question, where ...
3
votes
2answers
454 views
Is my EER Diagram correct? Why use relationships?
This is the first time I've made an Entity Relationship Diagram (ERD) for MySQL.
Prior to this, I would never create relationships between tables, I would simply create the tables in MySQLAdmin and ...
3
votes
2answers
159 views
Is there any better approach in designing this database?
CUSTOMER:
CustomerID (PK)
Name
Email
PAYMENT:
PaymentID (PK)
Amount
Date
PRODUCT:
ProductID (PK)
Type
Name
Price
PURCHASE:
CustomerID (FK)
PaymentID (FK)
ProductID (FK)
Quantity
Or ...
3
votes
1answer
171 views
In terms of databases, what does 'roach motel' mean?
Recently Larry used it in one of the OpenWorld keynotes.
Wikipedia says
In computers, sometimes as slang use:
Sometimes used to refer to a proprietary file standard -- "you can check ...
3
votes
2answers
219 views
Data Model For Summarizing Student Info
I work for a school district and one of the many things the admin wants to know is how many tardies a student has for a given time frame. I could certainly go retrieve the count from the attendance ...
3
votes
1answer
267 views
What scope(s) is “hasMany” a part of?
I realize, when talking about structures, obviously if you say "post hasMany comments" there are many records about comments relating to each post.
What I don't understand is what context these sort ...
3
votes
3answers
1k views
Estimate large database size and speed
First I want to know how to estimate the database size regarding the biggest table it will contains. I've the following :
+----------+------------------+------+-----+---------+-------+
| Field | ...
3
votes
1answer
70 views
Many:Many with Shared Relation
I'm modelling data with multiplicity like this:
Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I ...
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?
3
votes
1answer
102 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
168 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 ...
3
votes
2answers
117 views
How many fields is too much for a primary key?
I am creating a table to store information about the placement of objects in a grid (game map).
For example, I have a 10x10 grid and I want to store, in the database, that cell (5,5) has a blue box, ...
3
votes
2answers
138 views
Tablestructure for fast inserts/deletes with foreign keys
Current Situation
We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product.
Example:
id | competitors | some infos about competitor | ...
3
votes
4answers
347 views
MySQL unique constraint across two columns
I have a table defining relationships
Src smallint(5) unsigned NOT NULL,
Dst smallint(5) unsigned NOT NULL,
other fields
I need to add a constraint that says if a given values is present in one of ...
3
votes
2answers
3k views
Select column names whose entries are not null
I would like to have a list of those columns of a table that have at least one not-NULL data entries in them.
In other words, I would like to get the column names for which the following returns at ...
3
votes
2answers
313 views
How should I best design the tables and relationships, given the following rules?
In a Pawnshop business, customers pawn, sell or buy items. A contract which specifies the customers information, the items, and the terms and conditions is created whenever customers pawn, sell or buy ...
3
votes
3answers
182 views
Database design for changing number of fields
Google Contacts lets you to enter more than one phone number, or email address for a contact. You can add as many as you want. I don't know if there is a limit, other than practical list size. To ...
3
votes
2answers
309 views
How can I save a severely fragmented database/table?
I've recently made a discovery that might explain my slow SQL run times.
I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
3
votes
2answers
240 views
Designing an aggregation schema
The only thing closest to what I'm developing is Facebook's activities log for users. So I figured if I could ask about how FB might possibly implement such a feature at the database layer, then it ...
3
votes
1answer
201 views
Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning
I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
3
votes
1answer
218 views
Table structure for keeping track of multiple similar accounts
I'm creating a database to keep track financial information for about 30 different accounts. So far this has all been handled with each account as its own Excel table with the different years across ...
3
votes
1answer
244 views
How to use a relational database to store all users who belong to a group, and all groups which a user belongs to?
This is a beginner question:
A user can belong to many groups, and a group can contain many users.
Let's say the two tables look something like this:
user_id
user_name
user_email
group_id
...
3
votes
2answers
412 views
Good reason to denormalize?
I have a database with about 40-50 tables. All but 5 are part of a giant hierarchy of 1:M relationships that all point back to one solitary parent (call it "Project"). Each table is joined to its ...
3
votes
1answer
884 views
ERD via Chen's notation: How do I organize multiple 'last done' entries?
I'm having an issue creating a little practice ERD. I'm trying to do some more difficult stuff, and I'm not sure what the standard way to organize it is.
Basically, I don't know which of these is ...
3
votes
2answers
767 views
MySQL: Storing unique URLs
I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...
3
votes
3answers
249 views
Handling attributes that are time-variant in a Datamart
I have looked through the entire list of sites, and this is I think the best match. This is not really about database administration, more like database design. Please excuse me and point me to the ...
