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

learn more… | top users | synonyms (4)

3
votes
3answers
112 views

Schema for a lease platform

I have following tables: A room I want to lease (has user.id as ForeignKey) A users table for users who can rent it An availability table for time periods the room is to use with prices for that ...
1
vote
3answers
276 views

Remove duplicate values from query result

I am using Postgres 9.3.9. I have 2 tables : f_agent : CREATE TABLE f_agent ( f_agent__id bigserial NOT NULL, f_agent__total_users integer NOT NULL, f_agent__dim_time_id integer NOT NULL, ...
2
votes
1answer
495 views

Building a restaurant database

I am designing an Italian restaurant database, but I face some problems considering the database design, which lead me to a halt. The part that I find difficult developing is the dishes table. A main ...
2
votes
1answer
47 views

How to relate a table to other two?

I'm trying to design a database that contains, among others, entities "Photo", "Venues" and "Products". Given both Venues and Products could contain a collection of photos... how could I tell whether ...
1
vote
1answer
116 views

Is it ok to create additional column for helping other column to be a PK?

Let's say a Person can have more than one nickname. He(she) must enter his(her) nicknames alongside with other details: Here is the table columns: User_Id, Nickame, Age Of course, for now there ...
2
votes
1answer
77 views

Using a GIN index on an integer array to model sets of tags

In PostgreSQL, what are the pros and cons of using a GIN index on a column of type integer[] to model sets of tags, instead of doing a relation table? I am trying to design a simple data model with a ...
7
votes
2answers
3k views

nvarchar(max) converting to varchar and table optimization

I am working with a table that has all character types set to nvarchar some of them are nvarchar(max). We are converting all these to varchar and specifying a character width based upon the actual ...
2
votes
1answer
236 views

Best practice for when the ideal number of columns is unknown

I'm currently making a MySQL database to list products for an e-commerce site. Each product will have several sizes available which will each have their own dimensions and measurements (e.g. length, ...
0
votes
1answer
86 views

How to track changes to a record that spans multipe tables

I have the challenge to design a new database using a RDBMS, Oracle, to be precise. In this database there will be one specific record/document which may span multiple tables. With as little ...
1
vote
1answer
75 views

Product attribute schema design, mixing solutions

I am working on a product database in MySQL and the products can be multiple types with different attributes. The number of attributes and there datatype vary. I read about some design patterns (post) ...
0
votes
1answer
98 views

Multiple Cascade Paths (Not really) - Sql Server

In my database I have a table with 2 columns referencing the same column in a separate table. I know this creates a race condition, but I have a check in place to make sure they don't point to the ...
2
votes
4answers
130 views

Row for each date, or 31 columns?

Currently I have a system whose front-end looks like this: The following query: SELECT `l`.*, `s`.`month`, `s`.`year` FROM `summarylines` `l` JOIN `summaries` `s` ON ...
1
vote
1answer
191 views

Usage of json to store multiple attributes

Typically we have the table designed like this UserId FirstName LastName EmailId MobileNumber Age Sex City Where UserId is going to be the primary key Is it good to have multiple columns ...
2
votes
0answers
148 views

Database Design: Include Question Answer and Comment tables into Post table

I'm designing a database. This system has question, answer and comment. Question has some type of questions. In each type, there some differences so they're have some difference fields. Answer and ...
1
vote
1answer
32 views

Index design question

This query runs for 60 seconds: SELECT mychecklis0, .... FROM dbo.MyChecklistStatus mychecklis0_ WHERE mychecklis0_.AppFormSubSectionId=@P0 AND mychecklis0_.ProfileId=@P1 There is an index created ...
0
votes
0answers
17 views

Fill the 5th table with 3 other tables data in LibreOffice

I have 5 tables primary key foreign key felh(felh_id, felh_nev) gepek(gepid, felh_id, gep_nev) lic(lic_id, lic_k, lic_l, lic_db, gep_id) pr(pr_id, pr_n, pr_vr, lic_id) haszn(haszn_id, gep_id, pr_id, ...
0
votes
2answers
84 views

Managing tables in database

I am new to web development field and particularly very poor at database management. I am developing a web application which resembles Project Management System where new companies can add their ...
1
vote
1answer
33 views

Have several rows, or one single row with a huge comma separated data?

I have many workers processes working at the same time. They grab an ID from a list called available_ids, work on that ID, and put that ID on a list, called used_ids. The list available_ids can have ...
1
vote
2answers
49 views

How to structure different types of a model in MySQL?

I am building an application where users can apply for small business loans. There are 4 types of loans (A,B,C,D). There are 10 commons questions no matter which type they choose, but then based on ...
7
votes
1answer
462 views

Efficiently storing sets of key-value pairs with wildly different keys

I inherited an application that associates many different types of activities with a site. There are roughly 100 different activity types, and each one has differing set of 3-10 fields. However, all ...
0
votes
0answers
27 views

Data Warehouse for reporting - 6 Dimension tables,or 1 table and 6 views?

Problem: I have been asked to add a Dimension table to our data warehouse and create the ETL tasks to populate it. Lets call the table "DimInternalFault". The source table for DimInternalFault is ...
0
votes
2answers
66 views

Where to place a foreign key column? [closed]

This isn't exactly related to modeling the database but rather about "standardizing" column placement. Where should I generally put my FK column? As the last column of the table? At the start just ...
1
vote
1answer
127 views

What is the best SQL Server field type to cast to when storing a UNC path stored in a sql_variant field?

What is the best choice of field type to cast to when storing a UNC path in a sql_variant in Microsoft SQL Server 2008? It appears that I could store up to 4000 characters according to Microsoft ...
3
votes
4answers
952 views

Are junction tables a good practice?

Say I have a large table that holds the user's info and another table that holds several locations. Then I use another table that holds the user_id and the location_id. In order to retrieve the data ...
4
votes
1answer
203 views

Is this a sane model for a network provisioning system?

I'm sketching out a model for a network setup, where I have the following conditions: There are multiple nodes which contains a number of switches each. Each switch has multiple services, which may ...
4
votes
2answers
53 views

Snooping-safe Vote tables

I have a table with both user_id and vote. They are stored together so each user can only give one vote. However it's also problematic because admins can simply read who voted what. Is there a way to ...
4
votes
1answer
68 views

Single table or multiple table design when records have a clear distinction but are queried together?

Let's say I have the following types of recurring data (all types periodically come in). Post (Title, Summary, Text, Timestamp) Image (URL, Caption, Timestamp) Event (StartDate, EndDate, Title, ...
2
votes
2answers
821 views

Modeling invoices and orders

I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless. Model ...
3
votes
1answer
162 views

Modeling discounts - Product specific and Customer specific discounts

I have a reasonably simple database but have the following problem: I'm modeling a store + a lot of non-relevant stuff database. I have the following problem. I want to allow for specific products to ...
2
votes
1answer
1k views

Party Data Model / Universal Data Model Example

I am trying to implement Party Data Model / Universal Data Model in our application which needs to store different relationships between entities (People, Companies). Example of relationships: ...
2
votes
1answer
53 views

Database Table Design

I would like to ask couple of questions regarding my schema table design. I have the following data coming from an external database where I do not have any control over it. PROD_NO PROD_NAME ...
2
votes
1answer
263 views

Database design, many rows or many columns?

I am aware that this has been discussed before, but I've read conflicting information about this particular topic, so I'm hoping for a bit more decisive opinions from you. I have multiple tables, ...
1
vote
0answers
26 views

Uninitialized values: ignore or set?

From the implementation point of view for a database, is it preferable to handle optional form fields by setting to some default value (e.g. false, 0, "", etc.) or just ignore such fields (do nothing ...
1
vote
2answers
192 views

How to decide when is use ERD or UML for data modeling?

A UML diagram can be used both to model database designs and class designs, but an ER diagram should be used exclusively in database problems. How do you pick one graphical representation over the ...
2
votes
2answers
213 views

How to model an activity feed

I'm having a hard time modeling an activity feed that should show events based on user roles. I'm using Postgresql 9.4. I have a projects table. Each project has many users through a memberships ...
2
votes
1answer
117 views

table structure for events

Got a bit stuck, need a hand. I need to store events at the database, and events may happen at different location multiple locations at the same time so these are my tables CREATE TABLE ...
1
vote
3answers
146 views

Multi-table optimal database design

I'm a bit new to the database design. Thus this question to people who may have more experience. I need to design a database that needs to store statistical data for many systems. The data is ...
8
votes
2answers
477 views

Migrating from text and image to varchar(max) and varbinary(max)

I have a SQL Server database which contains a number of image and text columns, and I'm studying potential problems that might arise from migrating them to their non-deprecated counterparts ...
0
votes
2answers
291 views

Performance impact of adding a foreign key to a 1M rows table

I have an ecommerce database running MariaDB + InnoDB, which has a table to store the transactions (T) and another which stores quotes (Q). I'd like to make a connection between T and Q by adding a ...
0
votes
0answers
140 views

MySQL log tables design

I have to make 25 modification historic tables from 26 tables. I need a design for each table that could grow more than 75,000,000 of rows. I use InnoDB for 25 tables and 1 with MyISAM. I have one ...
1
vote
1answer
65 views

Can I somehow Improve the relationships between tables in the following sample?

I am currently developing a database for a school management system... There are tables for GRADE, SECTION and SHIFT... GRADE ==== id PRIMARY KEY SECTION ==== id PRIMARY KEY SHIFT ==== id PRIMARY ...
1
vote
1answer
185 views

SQL Server: Time Series Table Design

I am creating a time series database that follows the table structure here: Storing intervals of time with no overlaps. The difference with my design is that the FinishedAt column changes every time ...
0
votes
3answers
113 views

Is it better to use an ID (int) or a Name (varchar) as PK for a table? [closed]

Let's suppose I have an ORDERS_LINE table and a PRODUCT table and the ORDERS_LINE table must have the information of which product was ordered. I prefer to use a Primary Key based on the Name since it ...
3
votes
2answers
1k views

Is there a standard for naming foreign keys when two columns reference the same table? [closed]

I've always used the name FK_CurrentTableName_ReferencedTableName. I just ran into a situation where I've got a table with CreatedById and ModifiedById both pointing to the same (users) table, so this ...
10
votes
2answers
432 views

Conceptual ERD Multi-table many to many, or possibly recursive?

I'm creating a conceptual diagram [yes, I know I've included attributes and keys - but this is just for me to consolidate what I'm doing whilst learning] -- so please treat it as Conceptual with the ...
1
vote
1answer
37 views

should I change mysql platform for higher innodb_buffer_pool_size?

Currently Im running a 160G MySQL database on Xampp (Windows). But Xampp has upper limit for innodb buffer pool size of 4G. My computer has 16G RAM. So if I want to make full use of resources, should ...
-3
votes
1answer
76 views

How to organise huge mysql database for emails and search through it

I wanted to ask you for your opinion, what would be the ultimate and the best way to store million of emails and search on them, using MySQL. So first the product: You have tons of emails, which ...
2
votes
2answers
187 views

How to review Database schema?

I am green been software engineer, my team lead give me a task in PostgreSQL database, I need to determine the relationship of the all 405 tables, but the problem is the database doesn't have any ...
11
votes
2answers
247 views

Is it ever ok to not test a feature?

Is there any point at which you become so familiar with your language/database/system that there is no need to test a new feature/configuration/query/etc. by contained/simulated testing before ...
6
votes
2answers
244 views

Single table with more columns vs multiple tables with fewer columns

What would be a better database design for a social network website? A single table with more columns and fewer rows, or multiple tables with fewer columns but more rows? For example: A user can post ...