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

learn more… | top users | synonyms (4)

0
votes
0answers
6 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 ...
1
vote
1answer
14 views

Make a relation of a table with three different category of id's table [on hold]

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 ...
0
votes
4answers
70 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 ...
1
vote
1answer
19 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 ...
2
votes
0answers
57 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 ...
2
votes
4answers
76 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 ...
0
votes
0answers
21 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
1answer
256 views

Social file sharing database schema

I have a project called social media file sharing. I have this database schema and I need your opinion about what I should remove or add or edit on my database schema. Every user can upload media ...
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 ...
3
votes
1answer
116 views

Multi-tenant database constraints

I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other. My question is, is there any sort of way that I can use ...
4
votes
2answers
55 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 ...
0
votes
0answers
28 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
36 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
2answers
77 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 ...
1
vote
2answers
147 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 ...
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 | ...
2
votes
2answers
46 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
5answers
325 views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
0
votes
1answer
76 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 ...
0
votes
0answers
20 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
33 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
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 ...
0
votes
0answers
15 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, ...
4
votes
5answers
598 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
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 ...
6
votes
3answers
283 views

Need help on my first ER diagram

I just started my first Database course online, and I have a homework assignment to create an E-R diagram from a list of specifications. The specs are as follows: The company is organized into ...
3
votes
2answers
36 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 ...
7
votes
1answer
654 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
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 ...
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. ...
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 ...
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. ...
6
votes
0answers
172 views

Single table with more columns vs multiple tables with fewer columns

What would be a better database design for a social network website? A single table with more columns and fewer rows, or multiple tables with fewer columns but more rows? For example: A user can post ...
0
votes
2answers
49 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 ...
0
votes
1answer
33 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
1answer
394 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
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 ...
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
127 views

How to manage very large amount of data in mysql

I have a table in MySQL in which I write logs of users in a specified format. It have just 2 columns:id (auto-increment), log. Currently data is very less, so I'm using this. Now there is a ...
5
votes
0answers
60 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 ...
2
votes
1answer
192 views

PizzaShop - DB - Normalized Schema

I am trying to make a simple DB in Access for a theoretical pizza shop(the db is for an assignment). What I am trying to do is link one type of pizza to one or more types of toppings. I just wanted a ...
5
votes
1answer
74 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 ...
3
votes
3answers
85 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 ...
0
votes
1answer
412 views

“Entity Attribute code uniqueness” error in PowerDesigner

I am using PowerDesigner to do Conceptual Data modeling. If I use the same attribute name in more than one entities, I will get above error when I run "check model" or try to generate Logical Data ...
6
votes
1answer
58 views

Is it useful to split a table into a frequently used and a archive table?

I have a table with 1.000.000+ entries. Only the newest 100.000 entries are used very frequently. The other 90% are used very seldom. Is it useful to split this table into a frequently used table ...
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
1answer
51 views

What is the best way to store SSH fingerprints and public keys?

I'm building a virtual Keyring in our DB to allow users to store PGP/GPG public keys with relation to email addresses so that they can optionally encrypt all communications our system sends them. I'm ...
1
vote
1answer
47 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 ...