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

learn more… | top users | synonyms (4)

0
votes
0answers
3 views

One SQL Query on Product Database?

Consider the following schema of a product database: Parts(pid, pname) Suppliers(sid, sname) Catalog(sid, pid) sid in Catalog is foreign key and reference to suppliers and pid in catalog is foreign ...
1
vote
1answer
12 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
22 views

First time database creation, for college

So I have restructured my question based on new knowledge. So first thing first. I basically a student worker for a college department for career success, which is brand new and starting from ...
-1
votes
0answers
23 views

Review this ER Diagram?

You have been asked to design a database for the university administartion, which records the following information: All students necessarily have a unique student ID, a name, and, a ...
0
votes
1answer
21 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
34 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
0answers
11 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.
2
votes
1answer
39 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
18 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 ...
0
votes
0answers
11 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
11 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 ...
0
votes
1answer
33 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
30 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
28 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
12 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 ...
-1
votes
0answers
21 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
33 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
14 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[] ); ...
-1
votes
0answers
21 views

Writing SQL query for scenario [on hold]

I am trying to develope a shopping application. I need to write a query for the following table structures : enter image description here GRPMT : This contains the master group of Items(like ...
0
votes
0answers
27 views

how to desing a database for loan management in mysql [on hold]

I am not very well experienced in database design . I designed database tables for user and account information `but in case of designing table for loan management and fixed deposits.` I want to ...
0
votes
0answers
32 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
52 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
46 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
43 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
86 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
25 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
29 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
39 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
87 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
56 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
151 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
48 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
62 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
21 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
34 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
16 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
33 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
1answer
27 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
89 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
608 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 ...
0
votes
1answer
81 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 ...
3
votes
2answers
40 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
149 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
43 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
56 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 ...
0
votes
2answers
53 views

PostgreSQL surrogate keys: use sequence/bigint or OID?

I'm looking at a need for surrogate keys in tables I'm designing on a PostgreSQL-derived DBMS (Citus). Would OIDs suffice? Is there a downside to using them instead of creating a bigint field and a ...
0
votes
0answers
27 views

Is this solution correct?

We have the final relations of a database: Worker ( ID_W, Name_Lastname,Obligations, #ID_D) Department ( ID_D, Name_D) Vacation ( #ID_W, #BeginningDate ,Length_days) I have to draw the Conceptual ...
3
votes
1answer
44 views

What process would you use to determine references on a database that has no integrity?

Imagine you inherit a database which has a bunch of tables, no documentation and zero referential integrity. Now you're trying to find out what are the various relationships between different tables. ...
0
votes
0answers
30 views

Is double one to many a bad design? It looks duplicate when joining both one-to-many tables

Suppose I have user table, book table, DVD table. User can borrow n book, n DVD at the same time, a book or a DVD can be borrowed by a user. (I understand it is many-to-many situation in real life, ...
0
votes
0answers
19 views

Remove a parent entity in PowerDesigner

I have User table in a PowerDesigner logical model, which inherits from a base entity that has Id as int. I need to change this to string. I can't change the base entity's id type, as only one table ...