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
0answers
18 views

How to model a type that's related to one of a number of other types

A generic data modeling question: How would one model the database tables in a case in which an entity of Type A is related to one entity of either Type B or of Type C (but not both). For bonus ...
0
votes
4answers
89 views

What is a DB Link good for?

Here is defined what a DBLink is but I really understand for what kind of situations you need it? What I could imagine is a situation where I have a limited (in size like sql-server express) ...
3
votes
1answer
85 views

How do completely empty columns in a large table affect performance?

I have 400 million rows in a Postgres db, and the table has 18 columns: id serial NOT NULL, a integer, b integer, c integer, d smallint, e timestamp without time zone, f smallint, g timestamp without ...
0
votes
2answers
52 views

One-To-One relation is this a good practice?

I have a table "Place" (id_place, name, address,coor_x, coor_y) I would like to add for a place their "facebook page" "twitter page" "wesbite" and telephone My first idea was to create a table let's ...
0
votes
0answers
52 views

Credit Card database schema

I am not very good in database design so please if you can help me with this. I want to know what will be the best way to store it in mysql database Here is the selection I want as final Select ...
0
votes
1answer
35 views

How to define the table structure for measured data in sqlite

I have an embedded system with an sqlite database. How should I define the table structure for the measured data from 4 sensors? Each sensor give us every second an numeric measured value. The ...
0
votes
2answers
40 views

Design of MySQL

My supervisor asked to design a database by using MySQL. This database is used to store information about evaluation surveys. The survey has a lot of questions, and each question has 5 choices, the ...
0
votes
1answer
47 views

PostgreSQL function thinks table reference exists when it does not

So I run this query: SELECT f_copy_tbl('parts','parts1'); ...while working on code based off of this answer and I get this error message: ERROR: relation "parts1" already exists pgAdmin III ...
1
vote
1answer
75 views

Multi table GROUP BY query optimisation

Currently I have a multi table/column GROUP BY query which is pretty slow. Below is the tables schema used by the query: report Table ...
0
votes
2answers
102 views

One MySQL Database or Many MSSql Databases

At the moment I have an in-house Access database, data stored by our online survey provider, and data stored by our project management provider, both in databases. What I want is an in-house database ...
0
votes
0answers
14 views

Assigning items to containers

I have two tables for separate things: One table for containers, one table for items. They both have things like descriptions, title, and of course ID fields: container_ID and item_ID. My containers ...
1
vote
0answers
33 views

Implementing a consistent mailbox

I am trying to implement something like a mailbox for short messages between users. I am using Postgresql 9.4.1 with SQLAlchemy (Python3). There is a table of users: users = Table('users',metadata, ...
0
votes
0answers
14 views

Should I partition table by time or project_id, or both?

I have a table of event data that is getting slightly large. It's ~10 million records. I want to partition the table to help speed things up. I almost always restrict the query by both the time and ...
1
vote
3answers
98 views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
0
votes
0answers
12 views

How to add entries to tables in PostgreSQL if the attributes are reliant on other tables?

The problem that I have is something like this: Users ========= UserID Name Surname User Group ========= GroupID LeaderID Group Leaders ============= LeaderID UserID GroupID The problem arises ...
0
votes
0answers
29 views

How to combine 3 queries into 1 including set?

I have 3 mysql queries and each does specific job, the problem is I can't figure out how to combine 3 queries into 1. I'll write down all 3 queries and explain what each does, thus you can figure out ...
2
votes
1answer
47 views

Storing floating point values for various units of measurement (kg, ml, mm, pl)

I have a table like the following: order_item ------------ serial | id (PK) integer | order_id ???? | quantity integer | unit_of_measure_id (PK) --> ml, l, kg, mg, mm, m, etc. ...
1
vote
0answers
49 views

Help with ER design

So i am trying to create a db for a restaurant as a homework assignment.. However there is a spot that confuses me and involves the dish entity. "Every dish must belong to a category ...
3
votes
1answer
49 views

What is “cover-to-cover indexing”?

I came across this term reading my lecture notes; I can't find it on the internet. My notes say the following: Given a schema R(A,B) and a query of the form "A=a", or "B=b", or "(A=a AND B=b)". ...
0
votes
1answer
34 views

Zerofill Auto Increment - mySql

Ok, So I'm working on an app and I can't remember, for the life of me, why I set a zerofill on an Auto_Increment column when I created the database. And I couldn't exactly find any compelling info ...
1
vote
0answers
44 views

Database schema design for individual configurations

I'm trying to design a database schema that allows me to store customer custom built products. As a simplified example: A user can buy a cupboard, draws, boxes, and dividers. A cupboard has 2 tiers. ...
0
votes
0answers
19 views

what model should i use for store detections time series in mongodb?

i have cloud site with many hosts and virtual machines inside of it. I receive from some software many different metrics values regarding vms usage (cpu load,free ram,energy consumption and so on). ...
0
votes
3answers
334 views

Should login id and password be stored in separate table

I am working on my academic project. I have a table for employee records which have employee id, joining date, designation and other fields. I am trying to implement a scheme in which when record of ...
0
votes
1answer
33 views

Help with relational database design

I have a data model where each user profile belongs to a department. Each job is also available for a specific department. The department names are formatted like this: AAA-BBB-CCC-DDD-EEE, but of ...
1
vote
1answer
226 views

Optimizing Amazon RDS - MySQLTuner Recommendations / Feedback & Suggestions

I'm currently trying to optimize a database I've inherited for speed. The server is an Amazon RDS db.m3.large instance: 2 VCPUs 7.5 GB Memory 500 GB HDD My current strategy is to do the ...
-1
votes
2answers
32 views

Multiple instances of same foreign key in a single table

I have a table that references the same foreign key multiple times (4 times) in the same table. Can someone explain why/how this makes sense?
0
votes
0answers
38 views

All in one table or separate tables for each categories for a clasifieds website?

I am developing a classified website by using ASP.net and my DB is MYSQL. In there I am using a header table to store common records of an ad. like ad title, description, published date like wise. And ...
2
votes
1answer
239 views

Foreign Key NULL vs Foreign Key to an empty string

This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information) CREATE TABLE medicine ( id serial PRIMARY KEY, ...
1
vote
1answer
48 views

Which of these structures are good for maintaining history data?

Iam creating an application which is being used to send reminders. Here is the requirements. We have set of Students. Every student has a Student Pass, this need to be renewed in every month. ...
1
vote
2answers
94 views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's ...
0
votes
2answers
24 views

Loose database structure

I'm just thinking out loud. I'm wondering if there's any database technology where it's structure does not need to define upfront? For example it may start off with just one column or roll and user ...
0
votes
2answers
61 views

Product Attribute System While Avoiding EAV

I'm working on building an attribute system for products. The issue that I'm running into is that various products can have very different attribute requirements. Some E-commerce websites such as ...
0
votes
1answer
200 views

Soccer league ER diagram (implementing a many to many relationship)

I am planning on creating a database to track teams and players in a soccer league. The stats I wish to track are as follows... goals scored/conceded(gk), assists, minutes played, cards received and ...
0
votes
1answer
16 views

Will coloum size change when trying to use single unicode character in place of multiple ASCII characters?

I have a column in utf8_unicode_ci collation for text type in MySQL. The content is in Unicode, I have used tags like {br}, {v}, {t}, {h} etc.., throught the database. I am now thinking of changing ...
1
vote
1answer
73 views

Normalization to 2NF and 3NF

I solved this example to determine 2NF and 3NF. I would like to check if my answer is correct. Can somebody check the 2NF and 3NF of this example, please? R( A B C D E F G H I J) with functional ...
0
votes
1answer
39 views

Alternative to RDBMS: Modeling Exam/Testing data

I'm a born and bred relational database guy and C# programmer dude. IBM DB2 & VB.Net in school. TSQL & C# at work. I'm planning a project for fun: Designing a Testing platform that goes from ...
1
vote
0answers
26 views

How to query against legacy data that needs to be cleaned up?

I have an existing SQL Server DB with 3 tables (note: this is a made up example, but modelled after a real problem). Artists : contains music artists MusicRecords : contains information on music ...
0
votes
1answer
57 views

Copy of a database with an existing database with schema and data in sql server

I have two databases one is having master kind of tables and other will have transaction data. I should create a new database with a structure of an existing database to my clients. Here is the ...
2
votes
1answer
44 views

How to enforce an “exactly-one-per-group” constraint?

I have a table x that I've defined like this: CREATE TABLE x ( xid INTEGER NOT NULL PRIMARY KEY, yid INTEGER NOT NULL REFERENCES y(yid), is_principal BOOLEAN NOT NULL ); This definition ...
0
votes
1answer
30 views

Advisable number of foreign keys in a single table [closed]

I was wondering, keeping performance and optimizations in mind, while designing the database, is there any advisable number of foreign keys you should keep in a single table? At present, few of my ...
1
vote
2answers
37 views

How to normalize a table that contains a CK not included in the PK?

I have the following table. The PK is model_ID. Assuming that the modelName field is unique(that is, no two brands can have an identical modelName), there is a transitive dependency between modelName ...
0
votes
0answers
35 views

What kind of data cannot be stored economically in a database / data-warehouse?

Someone mentioned this in a presentation but didn't provide any examples. This stuck with me and I am trying to figure out what kind of data couldn't be stored economically in datbases ever since. It ...
2
votes
1answer
77 views

If A and B are candidate keys, then AB is candidate key or not ?

If R{abc} is a relation, f{ a->b b->a a->c b->c }, then a and b are candidate keys. What about ab ? Is it a candidate key or is it a super key or both ?
0
votes
0answers
17 views

How to model role and different activities

I am new in the database design and I'm facing this problem, I hope somebody can help me. In my domain we have 3 roles, Non-Supervisor, Supervisor, Manager. I created a Person table and a role table ...
0
votes
0answers
36 views

Manage orders based off product attributes and quantity

I'm developing a database for an online store in which the Products have multiple attributes and attribute types. ATRIBUTES TYPES atrtype_id name (like size, color...) ATRIBUTES atribute_id ...
3
votes
2answers
112 views

Is there a way I can speed up queries against this particular view?

I have three tables in a PostgreSQL database that I'm querying via a view and some joins. CREATE TABLE network_info ( network CIDR NOT NULL, some_info TEXT NULL, ...
0
votes
0answers
41 views

linking a single table (comment) to multiple tables (article,link,attachment)?

the main idea is that i can post an article , link or an attachment and i can receive comments on each of these types of posts (the structure of the comment is the same) , the problem here is that i ...
0
votes
0answers
83 views

Multiple user roles with different attributes

I need to design a database that has multiple users, with multiple roles. For instance, a user can be an admin, company, employee, or customer. Each user can have any number of these roles. Right now ...
0
votes
2answers
69 views

Normalising shared entities and applying constraints

I'm looking for a specific "best practice" or "pattern" concerning entities that are shared between different entities, having a relation to one of many. For example, one may have the generic entity ...
0
votes
0answers
43 views

How to store likes to posts in Parse.com database?

In my parse database I have a classed called Post and Like. Should I create a field likes in Post class with Relation<Like> type or there is another better option of storing likes in Parse DB?