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

learn more… | top users | synonyms (4)

2
votes
1answer
21 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
7 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
15 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
28 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
11 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
19 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
22 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
26 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 ...
3
votes
1answer
42 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
44 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
41 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
82 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
149 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
47 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
31 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
88 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
605 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
79 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
42 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
55 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
52 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
26 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
29 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 ...
4
votes
1answer
395 views

How to design a database

The file which contains the day to day activities of each employee. And it shows the detailed description of employee what they done at work time. So these files are prepared by employee. Now, my ...
0
votes
0answers
12 views

Table mapping _ updating

I have three tables with inserted data so I want to update Value.Value with H.facility and H.Dep and H.CAT. Actually table V is the metadata of table H and table value has FK to V. I want to ...
0
votes
1answer
34 views

Database design for actions on some entity

I am making a todo list kind of a service. One todo list will have several items , that can be done/deleted/snoozed etc. Users can also add a new item to the list , delete an item etc. I want to ...
4
votes
3answers
94 views

SQL - Many-to-Many relationship with same table and with relationship constraint

I have a SellerProduct table. Each row within the table represents product information as offered by a seller. The SellerProduct table has the following columns: id (serial, pk) productName ...
5
votes
0answers
61 views

Difference between contains and has in ERD

I am working on an ERD which represents the relationships between entities in a Web development company behavioral model. There I have entities like: Customer Service ----------- (like: web ...
7
votes
1answer
160 views

Data Warehouse: How can i query daily snapshots?

I have some snapshots of a database that are not timeseries. For example: Snapshot day 1: +----+---------------+------------+------------+ | ID | Title | Category | Date | ...
1
vote
1answer
50 views

Surrogate key resolution, or what am I missing in the Surrogate v Natural argument?

I've read a lot from both camps in the surrogate vs. natural key debate. What I am struggling to understand, and what seems to be glossed over, especially in explaining how surrogate keys are more ...
5
votes
1answer
77 views

Multi-Datacenter design for SQL Server

I'm not a DBA and perhaps I can't explain myself properly. I'm looking for some fresh ideas about how, and what I need to deploy, to get a full SQL Server database available across different ...
2
votes
3answers
38 views

Record inheritance in mySQL

I have been struggling with this for a little while. I want to have a set of calendar records. The top level will apply to a large group; the next level will apply to a sub-set and the final level ...
0
votes
1answer
24 views

What service that lets you create records/data into an immutable database storage system that cannot be deleted?

I'm not sure if this exists or not, but is there a database storage system that is immutable, whereby it cannot be erased or be altered without someone knowning? Use case would be for public data that ...
0
votes
0answers
28 views

DB table structure for many-to-many relationship

I'm trying to develop a relational DB design which stores data related to aerial survey(s) conducted off of the coast. The DB stores the actual data collected, as well as design parameters. The ...
0
votes
1answer
23 views

How to get capacity specifications for a particular Oracle release?

How to get capacity specifications for new Oracle release(Oracle 11.2.0.4)? ie, information like How much schema we can create?, How much tables a schema can accommodate? How much rows total ...
3
votes
1answer
58 views

Simple database structure advice

Newbie to dba. I'm creating a php/mysql inventory list and would like some advice before I start, on structuring my table(s) and database. The inventory database will eventually contain hundreds of ...