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

learn more… | top users | synonyms (4)

0
votes
1answer
37 views

Data Modeling constraint issue

Can someone help me with this school question? I have this data model shown below: here is the question: You are modeling an emergency communication system used to contact teachers in case ...
5
votes
3answers
106 views

Normalize repeated indefinite ongoing dates for an event

I am designing a calendar database that has a lot of booking and billing information. The database itself consists of form records, which are editable, and only when an event is confirmed by staff it ...
1
vote
0answers
56 views

SQL Server tempdb high IO

Using SQL Server 2016 Enterprise, having just recently upgraded from 2008 R2. The server has 192GB of memory, 24 logical codes, and is running around 30 databases with a total size of around 500GB. ...
0
votes
1answer
28 views

User based payment system table designing

We've been developing user based payment system. It's pretty much metering how much users use some contents per hour. And we send the bill at the end of every month. I'd love to know how user based ...
2
votes
0answers
29 views

How should I select a primary key in my use case

I have an app wherein I want to give offers to certain devices which fall in range of IMEI numbers (I have a separate algo to calculate this range). So I want to create a structure in which I have a ...
0
votes
0answers
20 views

Basic Inventory/Stock Schema

Previously, I thought I needed an "attributes" table based on their category/subcategory (Medicines have "Dosage" but Soap doesn't right?) ... but while trying an example I made up in my head, ...
3
votes
5answers
988 views

When should I mark a column as the primary key?

I have a table like this: // cookies +----+---------+-------------------+------------+ | id | user_id | token | expire | +----+---------+-------------------+------------+ | 1 | 32423 ...
0
votes
0answers
23 views

Traditional SELECT with Multiple Joins or SELECT JSONB then multiple SELECT

I am writing a simple recipe book app based on node.js with sequilize as ORM and Postgresql 9.4 as DB. Recipe has multiple ingredients that can be measured with different units. I store recipe steps ...
1
vote
1answer
26 views

Efficient way on updating multiple tags or categories

What's the efficient or best way on updating a multiple tags or categories? Currently I have posts and posts_meta tables. I have this structure on posts_meta table: +----+---------+----------+------...
0
votes
0answers
31 views

Normalization of an actual paper form | STUCK

I need your help and advice for my Normalization steps of an actual paper form otherwise, I will be doing mistakes again and again, unfortunately. My class fellows are not so great to help me either. ...
0
votes
1answer
24 views

Is there any database modeling software which provides a real-time (and editable) ERD, which I can manipulate alongside a traditional GUI? [closed]

In the web development world, the designer can write his code and watch the webpage materialize in real time. Why is this such a convoluted process when it comes to the database world? In MySQL ...
0
votes
1answer
36 views

Products relation structure

I'm trying to design a products relations table structure. There is an existing product table that has the following fields: A product could be related to other product but also to a category or a ...
0
votes
1answer
42 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 ...
1
vote
0answers
24 views

Database design - many-to-many relationship between three columns [duplicate]

I'm noob in database. So don't mind if its a silly question.This is used in my application and is the first one. I need a database design for it. value 4 is dependent on the other value 1,2&3. ...
-1
votes
0answers
27 views

Normalizing compound surnames

On the road of generating a unified Individual (person) database within a framework that admitted same person to become several IDs I've come to the the not so discussed dilemma of the compound ...
0
votes
1answer
66 views

How to use a table when the user identifier can be one of two fields

Recently we've started developing Github like organisation system for our service. We have both free and payed plans. There are users. Also there should be the owner for organisation and the owner ...
0
votes
1answer
35 views

Database extensions vs migrations [closed]

When using of database specific features (instead of 3rd party solutions) is justified, especially on the beginning stage of the project? Example: Postgres has full-text search, which do not support ...
0
votes
1answer
56 views

I Need Help With My Database Design for Product Management

I'm designing a MySQL database and the front end for my work. The database will store all of the products that we sell on our marketplaces and their relevant data. We will be able to lookup products ...
2
votes
0answers
57 views

Anchoring values for a rating or likert scale

I am designing a schema for an interview system as per the suggestion in this answer. I need to store several different types of questions: Questions with a free-text response Multiple choice text-...
1
vote
1answer
25 views

SQL Server Table Design for Report - Is OLAP Schema Suitable

A work colleague is developing SQL Server database tables for a reporting project. Basically, the only activity on the table will be populating it from a ETL process (SSIS). He insists on creating a ...
1
vote
1answer
59 views

Questions and answers database design for Core Data

I am designing database for a question and answer projects , and I am stuck. I do not know how to do it. Here is information: There will be many users There will be many questions Each question ...
2
votes
2answers
55 views

Database design - summary table vs always counting over tables

Imagine a system where a user can make a whole lot of different actions, like: Making a purchase Liking something Opening a specific view (pretty much every click) Inviting a friend ... The ...
1
vote
1answer
53 views

How to deal with a chain of several linked 1:n:n*m:n*m*k “join” explosion?

I have a domain model, where I have a "manager" which has 1:n relation with another resource (A). A has 1:n with B, and B has 1:n with C (it makes sense as a relation). However I'll need a lot of ...
-4
votes
1answer
74 views

How does indexes work in a database? [closed]

If I have a list of name that are all different and I have to find a specific name,how could indexes help.thi research?How does index work in a database?I have read other discussion but I haven't ...
0
votes
1answer
53 views

Set indexes for database used for login and register actions

I am thinking on how to set indexes to improve performance of a database for register and login actions.I have seen that alphabetical indexes are not good so I don't know how to do...Can you help me?...
4
votes
6answers
377 views

Database design for an exam

I am designing database for an exam, and I am stuck. I do not know how to do it. Here is the relevant information: Student answers 10 questions. Each question has 3 options (answers) and student ...
1
vote
1answer
37 views

Simple Database Design - Many to many? - First database (newbie)

This is my first database I am designing. However I am getting confused on the many to many relationships. I have a products table, each product can have inventories in many different warehouses. So ...
3
votes
3answers
176 views

Database architecture

I am in the early stages of designing a database, and I have little (none) database design/planning experience. The database is part of a job tracking engine.. As jobs are created, they are assigned ...
1
vote
1answer
23 views

Using Case with no column result

I made this solution but it continues not returning any value If the user not exists. select case when TypeId = 1 then 1 when TypeId = 0 then 2 when count(1) < 1 then 3 ...
1
vote
2answers
41 views

How to model that several variants of a merchandise item may affect its price?

I am an absolute beginner to relational data modelling. I hope my question will be acceptable here nevertheless. Let’s say there’s a merchandise. An item of merchandise may have variants. (While it ...
1
vote
2answers
37 views

Best practice for parent child relation

Suppose, there are some tasks and some sub-tasks under those tasks For example: Task 1 Sub task 1.1 Sub task 1.2 Sub task 1.3 Sub task 1.4 Task 2 Sub task 2.1 Sub ...
2
votes
1answer
59 views

What is the cardinality of entity relationship between Employee and Department?

Recently, a friend of mine asked me this question about the cardinality between a Department and Employee entities, I replied many to many, then he, my friend, showed me drawings from the internet ...
3
votes
0answers
106 views

Inputting auto-incrementing, grouped, unique hashes?

Alright, here's what I've come up with so far: SET @id=(SELECT IFNULL(MAX(id) + 1, 0) FROM leads); UPDATE leads SET id=@id WHERE id IN ( SELECT id FROM (SELECT * FROM leads) AS something WHERE ...
1
vote
1answer
38 views

Booking database design with a twist

I'm wanting to find a way to how I can implement a booking system that lets users put a start date on the booking and generate the end date whenever. Lets take a house renting example. Say I wanted ...
3
votes
1answer
27 views

How to decipher entity relationships in a Physical Data Model given the corresponding Logical Data Model

Can someone use the entity relationships in this Logical Data Model (LDM): to explain the entity relationships in this Physical Data Model (PDM): and how to specify the foreign keys in the PDM?
1
vote
0answers
14 views

does insert “logs or traces” into RDBMS (example log4j) makes sense? What are pros and cons?

Our developers are inserting Java logs into a table. This table can grow very much (for example debug logging enabled) and I need to to truncate table. Our database is for OLTP. > desc ...
3
votes
1answer
44 views

How would i create a report involving multiple many to many relationships

Lets say table A has a many to many relationship with table B, and table B has a many to many relationship with C. Two junction tables are created for AB and BC to form the many to many relationships. ...
4
votes
2answers
42 views

Database Design (how to handle subclass & superclass)

I'm wondering what is the best approach to this type of problem (I'm a highschooler so don't have practical knowledge on the subject): Let's say I want to store information about employees at a zoo. ...
1
vote
1answer
29 views

Design: How to account for a custom product in a transaction item table

This question is related to Database Design: inventory and sales system? So for example a burger is a Product which is made up of Component bun, meat, cheese, pickles etc. When this burger is sold it ...
0
votes
0answers
26 views

one Comment table for all content types or many tables for each content type

I have a very similar question with this stack overflow link but since its not answered I had to post it again. I have many place which people can place comment: file download, biography, blog and etc....
1
vote
2answers
30 views

Bridge table question

Should be a simple questions, I just want to make sure. If I had 2 tables I want to bridge, for example food (food_id, foodname, ...) and a plate (plate_id, platetype, ..). When the tables are ...
0
votes
0answers
32 views

Final Unique Index for Person and Companies?

I am creating a persons schema where the the person table must be very simple because it will be father of other tables that completes it, but a person can be fisical or legal, so I wont put any ...
1
vote
0answers
53 views

How does one map nested multi valued attributes?

Let's say one has an Entity E. E has a multivalue Attribute M1 and a simple attribute A1. However M1 consists again of a multivalue Attribute M2 and a simple Attribute A2. How would one map this ...
2
votes
1answer
62 views

SQL Server 2008: how do I hide data from a DB Maintainance User?

Our organisation employs a third party company to do maintenance on our database. To be honest, currently they act as our DBA's since we do not have one. In order to do the maintenance work, they ...
3
votes
2answers
46 views

Table with N associations

Suppose I have three tables: meta_tags, users, posts. (just an example) Users and Posts both could have tags, so my meta_tags table could be structured like meta_tags - user_id - post_id - ...
1
vote
2answers
140 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
votes
1answer
76 views

What are static tables & dynamic tables?

I heard the term "static table" when I'am learning about dirty reads in database. Can anyone explain what is a static table and what is a dynamic table ? Thanks in advance.
0
votes
1answer
43 views

Database design for changing data

I'm trying to design a database to store information about an entity. This entity will pass through 5-6 stages. In every stage may happen some property modifications. It is necessary to have access to ...
0
votes
0answers
46 views

Should I make a new table to store date?

I want to store date on which users sign up on my site. Since on a given date several users will be signing up, there will be repetition of data. Should I make a new table to store date to make it ...
3
votes
2answers
247 views

Non unique one to many relationship

I am a noob to database design so bear with me. I have two tables in Microsoft Access: Table 1: | ID | Field1 | Field2 | |:---|--------|:-------| | 1 | A | 1 | | 2 | A | 2 | | ...