The logical model and/or physical design of a database.

learn more… | top users | synonyms (4)

6
votes
1answer
603 views

Convert clustered primary key to non-clustered and use another column for the clustered index

I have a large table with a primary key (GUID) that is also the clustered index. There is already a integer sequence based field. So I want to leave the GUID as the PK and make the integer column the ...
5
votes
1answer
10k views

How to get the foreign and primary key details for a table in SQL Server 2008

I would like to use 2 queries, one that lists all the foreign keys for a given table with the following information Schema name, foreign table name and the other that lists all the primary keys. Is ...
2
votes
1answer
6k views

Using a foreign key on multiple fields in one table

Simple question probably. For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does ...
4
votes
2answers
25k views

How to Find the Foreign Key Associated with a Given Primary Key

All, I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via SELECT * FROM ...
2
votes
1answer
182 views

Will a primary key be added as a clustered index?

I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables. If I perform an alter table to assign a primary key will this ...
0
votes
1answer
37 views

Natural join question, I have the answer, but I don't know why it's correct. Help?

So I have a relatively simple database schema. I'm not sure why the where clause in the answer magically makes the dept_name duplication problem go away. I'm new to all this and I'm sure the ...
0
votes
1answer
23 views

Google datastore modeling help

I have a very simple requirement. I have an application where users log in, add/remove/view friends, and can send/receive (also view the message you sent and received) a message (message is more like ...
3
votes
2answers
72 views

Database normalization: Which is the best fit?

I have 3 different types of data set: Notification: start_date end_date notification_type country_id state_id Campaign: start_date end_date user_notification Transaction email ...
1
vote
0answers
16 views

Handling Login of SAAS application end users

I am designing a SAAS application with multi-tenant database per-user-per-database with unique sub-domain of subscribed users. The question arises when subscribed user of saas application add more ...
8
votes
3answers
142 views

How to avoid a cyclic dependency (circular reference) between 3 tables?

I have 3 tables: People Post Likes When I design the ER model it has a cyclic dependency: 1:N People --------< Post 1:N Post ----------< Likes 1:N People ...
0
votes
1answer
32 views

Database Design - I have a question regarding joining tables

Forgive me for my dumb question but I am new to Ruby on Rails and data design. We all have to start somewhere right? Well my question is that I want to build a database for a Truck driving company. It ...
0
votes
0answers
15 views

Dealing with multiple user types, modules and requirements

I have the following database schema. Basically, it all comes down to this: - users can have multiple user types - some user types come with specific requirements for a complete profile - there are ...
1
vote
1answer
214 views

When to use hashtable over binary search tree vice verse for Database Index

Database Index can be constructed using binary search trees (BST) or HashTables , From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions ...
1
vote
1answer
38 views

Apply Definition of MVD (multivalued dependency) to Example?

Supposedly this multivalued dependency (MVD) and this functional dependency (FD): {W} ↠ {N,D} and {W} → {N} hold in this table: V W N D ================= | v | w | n | 1 | ...
0
votes
1answer
77 views

Working on a blog creation website; should I be creating a new table for each blog, or keep everything on the same table?

So I'm working on this blog creation website as a side project. I don't actually expect it to become anything, but I want to make it as efficient as possible, even if just for practice. The idea is ...
1
vote
1answer
26 views

Model Entity Representation of an Organigram

I'm new to database and I'm trying to represent and organigram of a family (Great Grand father, Grand father, father, etc) using MER (Model Entity Relationship), and I'm quite confused on if I should ...
4
votes
2answers
77 views

Represent time interval in mysql Database

How can represent a time in interval in mysql database in a way that I can index them later . For example: -------------------------------------------------------- id | name | ...
-1
votes
2answers
91 views

How to delete millions of records from table

I am using postgree 9.4 database and I have a table snapshots with 700+ million rows and I want to delete about 9 million rows. I have tried different queries to delete records, but no success. Query ...
68
votes
11answers
23k views

Files - in the database or not?

What is the best place for storing binary files that are related to data in your database? Should you: Store in the database with a blob Store on the filesystem with a link in the database Store in ...
3
votes
1answer
59 views

Entity Relationship Design; The best scenario

TLDR; What would be the best design choice for the scenario below and how would each design react at extremely large data volume? I have experience working with large government database systems ...
1
vote
2answers
45 views

Sql tables design

I have a table with two columns: Brand(varchar) | optional_os_type(varchar) for example: +--------------+ | Lenovo | 1 | | Dell | 2 | +--------------+ Whereas 1 = win8.1 2 = win10 Currently ...
0
votes
0answers
36 views

Is this pattern named and how to normalize it?

I am given a data table : State ( StateId, StateNme, StateCounty1, Statecounty1Seat, StateCounty1Rep, StateCounty2, StateCounty2Seat, StateCounty2Rep, StateCounty3,...... ) ...
0
votes
1answer
11 views

library data modeling

I'm going to implement a library system as a web application. And I decided to use this library schema. But there are some problems which I don't understand. The Books entity, is it possible to ...
1
vote
0answers
36 views

database design for a filing system in educational website

i'm designing a database for a filing system in educational website. user can upload files ( files_table) user has a library (library_table) user can add a file to his/her library each library ...
5
votes
2answers
463 views

Relation between first name and last name

I have some troubles understanding the relation between the a person's First name and Last name in a table. (In a Relational Model) I've read that the relation is of type: 1-1: They are linked ...
1
vote
1answer
77 views

Should child comment be treated as separate table?

I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this? Current Table Scheme (1 ...
3
votes
0answers
35 views

why some one will use approximate data types for sales

I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
4
votes
2answers
80 views

Where/how do I save extra data per column?

Next to the actual data I have in a table, I need to save some data about those fields. I'm hesitant to call it metadata, because while it is exactly that, metadata has a specific meaning within ...
0
votes
0answers
22 views

Help creating database schema to build database off

not sure if this is the right area for a question such as this but I am looking to create a database for an IS warehouse who deliver laptops, desktops, monitors and other peripherals to different ...
0
votes
0answers
22 views

When is it suitable to turn a table around [duplicate]

When I create tables to store data in, I have columns for each property of the model to store. IMHO this type of table design is easy to read and logical. Example below: uid | firstname | lastname | ...
2
votes
1answer
1k views

How to handle huge table

We have a problem with a few tables that are very very big slowing down the system. so my question is if that size is normal for the amount of rows in the table, and also what could be done to make it ...
1
vote
0answers
20 views

How to bucketize using Query optimal bucketization algorithm

I am trying to implement query optimal bucketization algorithm (QOB). The input of the algorithm is D=(V,F) where V is the value of the attribute and F is the frequency of occurrence. For EXAMPLE D ...
0
votes
1answer
36 views

Generate incrementing IDs without sequences

Suppose there's a multi-tenant application where users can create some kind of documents with basic structure like CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT); CREATE TABLE documents ( ...
3
votes
1answer
40 views

Seeking feedback on table relationships, general schema design

I'm designing a database for a business that sells tickets to a variety of events worldwide. The app will be built with Rails using PostgreSQL. Here's what I have so far, link to full size image ...
5
votes
2answers
163 views

Dog Adoption Database Design

Apologies if this isn't the norm, but I am seeking advice for the database design for 'dog rescue/adoption'. I've done my normalization research and this is my first attempt at database design. Any ...
15
votes
6answers
3k views

Is database normalization dead? [closed]

I've been brought up old school - where we learned to design the database schema BEFORE the application's business layer (or using OOAD for everything else). I've been pretty good with designing ...
0
votes
1answer
23 views

Hibernate modelling a N:N Relationship

Lets say that we have two entities Entities: Bus (idBus*) Stop (idStop*,stopDescription) Where a Bus passes from many Stops and vice versa, therefore the cardinality is N:N. My ...
3
votes
4answers
242 views

What architecture is best to store a large 3 dimensional array in MySQL?

I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy: { x, y, ...
1
vote
0answers
40 views

Database for buses timetables

I have to design a database for buses timetables. Entities: Bus (idBus*) Stop (idStop*,stopDescription) Line (idLine*,lineDescription) Position (lat,lon) Some constraints are ...
1
vote
1answer
22 views

Google Datastore model User and Messages efficently

I have two kinds: USER and MESSAGES. A user can send/receive many messages to/from another user. Thinking in a relational way the relationship would suggest a user can have many messages, and a ...
1
vote
1answer
28 views

Need help making a sane database schema

I am writing a backend for an application that has 4 main types of content: a message, a post, a thread, and a thread reply. Right now, these are all stored in separate MySQL tables: a table for each ...
3
votes
1answer
37 views

Normalised database with one-to-one relationship

Is it bad practice to use a 1-1 relationship between two logically distinct tables: the user details and the user configuration settings? Each configuration record would have a single unique user ID, ...
3
votes
1answer
133 views

IS-A relationship with only single sub entity

Hi I'm trying to design a database for a dictionary. The words has several fields like meaning, an example, and maybe a picture. but the words that are 'verbs' has three more special fields that the ...
-2
votes
2answers
79 views

Random numeric string as a primary key - security, performance, efficiency?

I'm building an app aimed to store lots of records (millions) and trying to find a best way to store records identifiers that is publicly accessible from the web (record URL). I'd like to get: ...
0
votes
1answer
62 views

Schema design for products with multiple variants/attributes?

I'm using MySQL. The idea is similar to shopify with a different concept, so users are going to add their own products with multiple types of variants and attributes. From all the research I've done ...
0
votes
2answers
40 views

Optimal database structure for fast inserts

I am using MySQL Percona server. This table needs to do fast bulk inserts using 'INSERT INTO ... VALUES' for smaller requests (about 100k records) and using 'LOAD DATA INFILE' for larger (up to 10kk ...
0
votes
0answers
44 views

How much normalization is too much

I am working on designing a database for a site I am trying to build, I created what I believe to be the best normalization strategy, to limit the size of particular tables, and the number of tables ...
2
votes
1answer
25 views

Turn off auto qualification of table names when creating a view

Problem Definition I am trying to modified a shared development database, for a dev team to use for application development/testing. Most of the collective work (table names, views, etc) are stored ...
2
votes
1answer
47 views

Adding foreign key that references a column which is not Primary or Unique key

I got into trouble when I want to limit values of Table A's column to be taken only from a set of optional values in Table B's relevant column. Problem is table B's relevant column isn't a foreign ...
1
vote
0answers
18 views

Is there a design model for tracking processes done to an item?

I am working on designing a database model that involves tracking processes almost like an assembly line. There is only one type of item (lets call it 'unit') that has about 10 different tasks done to ...