Questions tagged [database-design]

The development of the conceptual schema and/or the logical model and/or the physical settings of a database.

Filter by
Sorted by
Tagged with
1
vote
1answer
133 views

Are there situations where queries are formed geometrically?

The topic came up in this other question. We were discussing sign value on a primary key having any significance. If there are good times to use geometry in building queries the sign value could be ...
1
vote
2answers
3k views

Configuration Table

Can someone explain what is the difference between a Configuration table and a Data table in a database? And how to design or implement Configuration table? I'm using MySQL and the requirement is to ...
1
vote
1answer
144 views

Multiple one to many relationships in series

I am making a DB for storing reports that have following fields: Ad_system, Ad_Campaing, Ad_Group, Keyword, Date, Impressions, Clicks, Conversions Logic is that, Ad_system can have multiple ...
1
vote
1answer
111 views

How to prevent null foreign key during database normalization?

I'm rather new to databases and am having a hard time trying to figure out how to structure my database. This database contains students, classes and all their grades both overall and in categories (...
1
vote
2answers
2k views

Optimizing query with type casting

I had a query that was taking way too much time despite having all the indexes I could think of. Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to ...
1
vote
1answer
65 views

Design a db table where rows may have NULL in multiple columns

I am trying to design a database for tour packages. Tour packages contains the detailed itinerary. Tour package duration may be between 3 and 30 days. I need to add the description of each day. If I ...
1
vote
2answers
184 views

Efficient workflow/queries to save unique information?

I am saving company information in a database. There is a single table with an auto-incrementing key that is unique to each company. There are separate tables for emails, websites, phone numbers, and ...
1
vote
1answer
930 views

How should I handle Products with different attributes in my database structure?

We have an e-commerce site (web application) built with PHP. There are different types of products like mobiles, tshirts, watches, books. A mobile can have attributes like, for example, ram, model ...
1
vote
0answers
119 views

Schema for social network using the supertype/subtype pattern

Scenario I'm trying to come up with a schema for a kind-of social network app, where users can publish Posts (and photos inside Posts), and also manage a private Diary that has also Posts (not sure ...
1
vote
2answers
601 views

I don't know how to read many to many relationships

I'm reading this tutorial, and I have question about it. Look at this picture: Looking at Physical Many-to-Many Implementation I read the relationship this way: 'A user can be in zero or more ...
0
votes
2answers
236 views

To normalize or not

I'm scraping data from a website and they have pages for Users and Groups. The pages are pretty similar; id, name, description, created date, number of elements and custom background. They also have a ...
0
votes
1answer
90 views

What do you call numerical coding systems that are left-aligned?

I'm writing an importing mechanism for the NAICS database. I have a few questions about this code-format. I've seen it before and I like it the setup. I'm going to ask some other questions about best-...
0
votes
1answer
43 views

String templating technique

I am working on a fitness app that is going to deliver notifications to the user. I intend using MySQL events to populate the necessary tables, and then using an Ajax timer or Websockets or whatnot to ...
0
votes
1answer
28 views

Unable to create view with temp table

Hi I am working on one project and want to create a view. I am using temp table in my query. can any one let me know how we can create such view ? Following is my query, I tried using WITH..AS , but ...
0
votes
2answers
1k views

Multiple rows v/s multiple columns

My requirement is as follows: An app with internationalization support and starting with EN and FR versions of text. So, I have 2 approaches for data in the table: 1. One column for each language: ...
0
votes
1answer
21 views

How do I make a citation table where work can't refer to itself?

CREATE TABLE Work(id INT NOT NULL AUTO_INCREMENT, title CHAR(20), date DATETIME, description VARCHAR(1000), CONSTRAINT Work_PK PRIMARY KEY (id), ) ENGINE=InnoDB DEFAULT CHARSET=...
0
votes
0answers
18 views

How to prevent a circular citation?

This is a follow up query of this I am using : mysql Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 CREATE TABLE Work(id INT NOT NULL AUTO_INCREMENT, title ...
0
votes
1answer
54 views

Database design help. many-to-many relationship

I have four columns. All columns are varchar fields. Three of them are independent and distinct fields having many to many relation between them and the fourth one is dependent on the combination of ...
0
votes
1answer
309 views

sub and sub-sub item organization system [duplicate]

So I am looking to find the best database design for the following situation. I will refer to my entities as 'articles' as that would best/most-easily describe them. So the articles have already ...
0
votes
2answers
53 views

How to structure Tables with changing status without duplicating data

I have a table called Inquiry it will contain many - Services, Commodities & Invoices. Then later, You can change it to an Appointment which will contain the same Services, Commodities & ...
0
votes
0answers
46 views

Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design?

I have a functionality that has multiple (7+) potential properties to be added via a central 'hub' table. In most cases there will only be one property assigned, but it's possible the user will want ...
0
votes
1answer
254 views

when to put a business rule in the business application layer or into a database constraint

I need you wisdom, I am debating with another person when is it recommended to put the code of a business rule into a database constraints or inside of the application business layer? There is a ...
0
votes
3answers
805 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
856 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 ...
0
votes
1answer
38 views

Searching for scalable storage solution

In one project I save statuses of processing in MySQL table: `id` `name` `email` `has_apples` `has_bananas` 1 Bob a@b.c 1 0 2 Mike c@d.e 0 1 3 ...
0
votes
0answers
434 views

Different product attributes for ecommerce store

I'm working on an ecommerce database but I'm having difficulties when working with products attributes, such as size and colors. For example, I have this table where I save each product attribute: ...
0
votes
1answer
205 views

EAV table question

What's the best design for taxonomies in EAV tables? taxonomy id autoincrement name unique terms id autoincrement tax_id references taxonomy(id) name text unique value text ...
0
votes
4answers
150 views

SqlServer: can one table's PK be the FK of other two table's? [duplicate]

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible for the project_id and ...
0
votes
1answer
779 views

Database-design for a small filter / search tag function

I'm building a search function in php for a database with 1k to 4k articles. It's going to be built on a tag system. I've been reading some tutorials, blog posts, q&a etc about it. Most important ...
0
votes
2answers
2k views

SQL Server 2012. Update second table based on input from first table

I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the ...
0
votes
1answer
85 views

Modeling a graph of different node types and edge types using SQL Graph

I am looking into using SQL Server 2017's SQL Graph feature to model a graph that has different node types and edge types, but am confused as to how I would model this. In short, a user can create a ...
0
votes
2answers
854 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
1answer
1k views

MySQL table with multiple values in one field

I'm building a database for a real estate company. The real estate properties are managed by sales people using a CodeIgniter website. How should I proceed with the database design for this table? ...
0
votes
1answer
3k views

Error 1239: Incorrect foreign key definition

While creating primary keys and foreign keys for my tables I got the error 1239 and apparently I'm the only one having this error as I could not find anything with a Google search. I was wondering if ...
0
votes
1answer
1k views

Database design : multiple category of users login into same system

In our new system we have three type of people Business owner Assistant of business owner Staff (a skilled employee) I am having following entity [Business] - include detail about business owner [...
0
votes
1answer
192 views

A (simple?) solution to the “logical decoding / sequence” problem in Postgres?

After reading this mind-blowing answer by Craig Ringer to my previous question Achieving "sequence number", rather than dateTime, for updates; postgres specifically I've become interested in ...
0
votes
1answer
172 views

What does a CONSTRAINT have to do with my unique index?

I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
0
votes
1answer
2k views

Benefits of using Staging Database while designing Data Warehouse [closed]

I am in process of designing a Data Warehouse Architecture. While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly ...
0
votes
1answer
298 views

Trouble in Multi-Language DB

I'm working on a DB design for medicines in many countries with different languages. I created a table for the different languages I want and link it to another table of chemicals. But I find that ...
0
votes
1answer
633 views

Split data across databases/machines

I have two questions about how to partition data across servers. Can I create and connect two or more databases, with the records correctly associated, using only a mapping table stored in a third (...
-1
votes
2answers
503 views

What are the possible risks involved in partitioning large database tables? [closed]

There has been much talk about the benefits of table partitioning in Relational DBMS. I would like to know: Are there any risks to table partitioning ? What measures are there to take to minimize ...
-1
votes
2answers
536 views

How to store tax in the database when tax consists of multiple taxes

We need to design Tax table for Billing System. Lets say we have a SGST (State Tax) and CGST(Central Tax) and Final Intra-State Tax = SGST + CGST Tax table id name rate Tax table id | name ...
-1
votes
1answer
101 views

How to optimize a slow select query with tuples in WHERE clause

I am currently trying to optimize a select query which takes 5-10 seconds on tables with 50k rows (and will grow 100x this soon). The purpose of this query is to find which tuples from a given set of (...
-2
votes
1answer
99 views

How can I design a database for different products(with different attribute) as well as we should keep in mind future new products requirement?

my question is very similar to question ,But i don't want to use EAV design Rigth now ,we have these products in our store Harddisk ->size(GB) ,speed ,price Shirt -> size(M-L-S) ,color ,price Book ...
-3
votes
1answer
52 views

From a backup strategy perspective, would it be efficient to use a relational DB (as MySql) and a document oriented DB (as MongoDB) in the same app?

I am developing an app where I need to store an undetermined number of properties for any given entity, for example: Entity || # of properties** Apples || 14 Oranges || 4 Pears |...

1
6 7 8 9
10