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

learn more… | top users | synonyms (4)

0
votes
1answer
36 views

Internal Online Store Database Design Issue

I am trying to create a database for an internal store that we are developing to manage our internal items (like phones, computers, office equipment, etc). The issue Ive run into is that they want ...
2
votes
1answer
93 views

Is it “OK” to have a table that only contain foreign keys?

In "OK", I mean "it doesn't have any bad impact with the overall database design". For example, will it cause insertion problems? or will it be a problem in the future when I add field in the ...
3
votes
1answer
46 views

Database design for products with bundles of products

I am building a database system for my retail business. I have set some tables which are: Product Purchase Sales Balance All are connected one another and are able to show my inventory level. The ...
3
votes
1answer
90 views

Most efficient way of caching queries / processed data

I have doubts on database design. I'm trying to know if there is a way to increase query performance, and the easiest way for me to explain my doubt is with a small example. I have a news website ...
3
votes
2answers
51 views

How to model A Shop Database

I want to draw the ER model of a store which The scenario is like this: They have list of Goods and Some Seller which sells them to customers they want to know how much they get profit from their ...
1
vote
1answer
44 views

Database structures for similar profiles

Please bare me if my question is too blunt I am working on a social network where I need to show similar profiles to users Every profiles has 40 parameters to match with other profiles. I want to ...
1
vote
0answers
59 views

Storing and searching user location using latest MySQL 5.7 spatial features

I want to use MySQL spatial features to store user location. I have read about MySQL spatial geometry data type. According to this article this works very well in MySQL 5.7 ...
6
votes
2answers
215 views

Efficient Database schema for multi-lingual content?

Assume I have a table for blog posts, posts, and schema as id author title content and each post can belonged to some categories id name descrtiption So a blog post can belong to multiple ...
1
vote
0answers
67 views

Creating relations using tags

I have three tables: Product, Accessory and m:n relation between Product and Accessory. There are currently around 200 products and around 600 accessories. The user wants to have tags on the ...
2
votes
3answers
83 views

Integer ID vs varchar ID with JOIN

I have a table of users but I don't know what is the best practice between using a unique MEDIUMINT identifier or a unique pseudonyme identifier using VARCHAR. With MEDIUMINT identifiers, like the ...
1
vote
1answer
35 views

Two tables with same field

QUESTION: I have QUOTATION table and SOA table quotation - table is for pending and not approve quotation. SOA- table for approve quotation. i will just transfer the data from quotation table to ...
0
votes
1answer
45 views

MySQL - design help for scale

I will be using Amazon Web Services RDS service to host a MySQL 5.7 server (Currently AWS do not support 5.7, but they have announced they are working on implementing this version and it will be out ...
0
votes
2answers
72 views

Database Design - Voting System

I'm creating a website in which people can submit there own lists, and then essentially others Up/Down Vote the items within the list. I'd like the list (If the submitter allows "votes") to be ordered ...
0
votes
1answer
35 views

Relationship between data independence and database schema

What is the relationship between data independence and database schema? I know both of them separately. But I don't know how to explain the relationship of these two.
3
votes
1answer
44 views

Explaining the problem with a column of values from a non-mutually exclusive domain

I'm trying to convince the powers-that-be that their current database design is risky and inefficient. I'd like to verify whether my assumptions are correct, and I'd like help in finding technical ...
0
votes
1answer
20 views

Design - duplicating properties vs not allowing delete

I'm designing a database for a mobile app CMS that contains user data. These objects are Address and Job (a job can have 1 address and an address can have many jobs). The first version of a similar ...
1
vote
0answers
18 views

Question on comparing two fields in Access

I'm not sure if the title is fitting as I am not 100% sure if what I am doing is correct but here goes. Here is my enrolment table, I have added a field called pass/fail as you can see which is ...
0
votes
0answers
18 views

How can I make microsoft Access update fields that are linked in several tables?

I am working on a database for clinical observations in cancer mouse models. Usually, the animal care staff reports an observation that the vet checks. Sometimes, we have false positives that I need ...
1
vote
1answer
39 views

Query that compares dates

I'm trying to create a query that shows if students are mature or not, based on their date of birth. However when the query is executed it returns all students as not mature. Any students that are ...
0
votes
2answers
47 views

Can this be further normalized?

I have to define a system where we will be storing Image data and which can have an arbitrary number of attributes for the Image added per client. I have designed the following table Image ImageID ...
2
votes
1answer
57 views

Cascade deletes from multiple tables

I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating ...
0
votes
1answer
15 views

Access IFf Query

I'm new and just starting out at creating databases so I apologise if noob question. What I am trying to do is update my Staff table to show that "Sarah Avon" has married and her new last name is ...
0
votes
0answers
26 views

Relational database specialisation with constraints

Recently, I got the idea of inheriting in Relational databases, but I need to understand some things on which I'm not clear. So, let's suppose that we have these entities: Enterprise, BankAcount, ...
2
votes
1answer
48 views

What exactly is metadata? How is metadata different than regular data?

As I understand, metadata is data about data- so I think of analytics; is my simple definition correct? How is metadata different than data? I'm simply asking to broaden my understanding-thanks!
0
votes
0answers
15 views

How to properly implement a relationship between a table with an array of foreign keys and the foreign table? [duplicate]

I'm not entirely sure how to implement this kind of relationship correctly. As an example, assume that we have: CREATE TABLE menu_item ( mi_name TEXT PRIMARY KEY, mi_ingredients TEXT[] ); ...
0
votes
0answers
37 views

Are my two proposed solutions on modelling resources inventory decent?

In the project management system that I am developing, I am wondering what is the best database design for keeping track of the resources inventory. While I want to determine what resources are ...
4
votes
1answer
62 views

Make a relation of a table with three different category of id's table

I have an issue There a table that's called "Routing" this table is for routing a task for three category of object - Analyser (interne machine) - subcontractor (externe ) Manually ...
2
votes
1answer
53 views

Database design for cancer mouse models

This is a follow up question from my previous post: Does this simple database design work even if it's cyclic? I am a graduate student working on breast cancer. My mice are genetically ...
0
votes
1answer
46 views

Store massive values data from remote controllers

I implemented telemetry system in order to observe state of remote objects. Every object has a controller collecting data from some sensors (about 10-30 controlled parameters, lets take average value ...
0
votes
4answers
101 views

Avoid using Many to Many Relationship

I have a 3 tables, Employee, Languages, Employee Languages. Now, Employee table is a profile table. Nothing special(Id, first name, last name etc.). Languages table has two columns: Language ID ...
0
votes
1answer
27 views

Every “INSERT” query check a combination of column's records

Unfortunately, my mind has stacked right now and i would like to design a really simple DB schema. What i want? In a simple table, for instance Table1, there are two different columns, Series and ...
0
votes
0answers
48 views

Sql multiple table checks for polling

I am implementing long polling using mysql for a near realtime data update for clients. Currently, this is what I have (and it works): A messages table, that holds messages between users,there is a ...
0
votes
1answer
41 views

Why do I need this attribute?

This is my relational schema; Book(title, shelfmark, ISBN, author, year) Genre(name) GenreBook(name, ISBN) User(firstName, lastName, cardID) UserBooks(cardID, ISBN, position) Why do I need the ...
3
votes
4answers
108 views

In a one-to-one relationship, where should the foreign key be placed?

I am currently designing an ERD for a project management system. I am unable to choose between two workable solutions as the best solution as I wish to embrace the best standards. The two entities ...
4
votes
2answers
59 views

Does this simple database design work even if it's cyclic?

I am a graduate student working on breast cancer in a veterinarian facility. I am designing a database to keep track of the health of my mouse colony. Each day, animal care staff verify each cage ...
1
vote
2answers
157 views

novice-design: Are FOREIGN KEYs always necessary? Joins on columns which are not PK

I've been wondering if following approach is bad or really very bad:). Question#1: can we use indicator_id to combine data from indicator_values and indicator_details tables (without using FOREIGN ...
2
votes
2answers
65 views

Is better to create a table for each room or make a single table that handles all the rooms in my case?

I need to make a server-side for iOS and android application that does something similar to chat(Is not really a chat), and I'm a PHP and MYSQL newbie. The process is that 2 users join a room and do ...
3
votes
0answers
72 views

How to relate a [ parent common] table with (possibly many) subtype tables?

I am having a bit of a brain freeze. I have a table whose properties are the root of 4 distinct elements, for example: I need to relate SystemType to a SystemCommon, where one SystemCommon can have ...
0
votes
0answers
24 views

Schema Design for multiple many-to-one relationships. of which exactly one should exist

Say I have table A, B, and C. B and C can both be related to multiple A's, but each A should have exactly one of either B or C related to it. Which database design is better: A: a_id, b_id NULL, ...
2
votes
0answers
42 views

Design a many-to-many table for scale

I am thinking about scaling issues with many-to-many join tables. I have a constructed example as following: Many users have many wishlists. On every wishlist are many items. If I scale this ...
0
votes
0answers
17 views

Sum data from 3 tables(joining), but one of may no data then other tables data will show

I am making a stock group summary report for this I am making Sum data from 3 joining tables, but one of may no data then other tables data will show. I have 3 tables as: ac_stock_item, ...
1
vote
1answer
37 views

Design database -

I want to create database. I have two kind of clients which admins and employee should communicate with them ; Customers and Manager. I'm not sure about which place employee's place on database. is ...
0
votes
2answers
46 views

Relation in 2NF with a transitive dependency?

Tomorrow I have my final exam of Database Design but I have a doubt about the Second Normal Form (2NF). The definition says a relation is in 2NF if no non-prime attribute is partially dependent on a ...
3
votes
2answers
122 views

Survey Database - Respondent Table (1NF or 3NF - Pros and Cons)

Building out a Survey ERD ODS layer. Now i'm debating on the design for my Respondent Table. Not sure if its best to have it as 1NF or 3NF. FOr a quick back story I get a survey extract with ...
4
votes
5answers
720 views

Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

I have a table and need to add a new column without a default value: Constraint: ALTER TABLE integrations.billables DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables, ADD ...
1
vote
1answer
151 views

How to remove data redundancy in product table if multiple suppliers are there for same product

I am designing a database where I have different products and suppliers. For example, I have a product, "mobile phone", say an Apple 5S. This will be sold through my website from different suppliers ...
4
votes
2answers
60 views

MySQL: Soft Delete post likes

I have implemented a like/unlike features for posts in one of my projects. The like table is normalized and has a column visible. The schema is as follows: Column Type Null Default ...
5
votes
2answers
154 views

Trigger to create symmetrical rows

I've got a table that describes some relation between two items. The items are referenced by ID. I'd like to create a trigger, so whenever you create a row (a,b), a symmetrical row (b,a) is created. ...
3
votes
2answers
58 views

Is it possible to display this restriction in an ER diagram?

I have a question about modeling a particular kind of restriction, but I don't know the technical term for it, so I will illustrate it with an example. I wish to make an ER diagram for a hotel room ...
1
vote
1answer
71 views

Data structure design for supporting arbitrary number of columns in table or database [closed]

I am currently working on a sort-of a meta-modeler to build a free web service so that people can input data and run several models on that data. The task I am currently struggling is: user needs to ...