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

learn more… | top users | synonyms (4)

0
votes
1answer
28 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
56 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
116 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
61 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
71 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
74 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
25 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, ...
3
votes
0answers
46 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
18 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
55 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
131 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
784 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
161 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
61 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
66 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
81 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
149 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
50 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
34 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
34 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
399 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
13 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
51 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
169 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
1answer
214 views

ERD for a web development company

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
209 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
66 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 ...
2
votes
3answers
53 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
26 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
31 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
26 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
64 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 ...
6
votes
1answer
69 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 ...
1
vote
0answers
15 views

how columns ordering in sql server database table impact performance? [duplicate]

As I read about database design from logical model to physical databases, I noticed that the column ordering may need to be changed the sequence recorded in the logical data model. The DBMS is SQL ...
1
vote
1answer
30 views

If my understanding of BCNF is correct, how did we know that R1 was not in BCNF?

Given that R(ABCDE) AB->C BC->D CD-> E DE->A Since, B is the only attribute absent on the right-hand side, we would start with B. {B}+ = B . Not a CK. Next, we ...
0
votes
1answer
21 views

What should be the rule for excluding an FD from consideration while doing BCNF or any other Normalization?

Let us consider this example: http://www.mathcs.emory.edu/~cheung/Courses/377/Syllabus/9-NormalForms/examples.html At the first decomposition step, it is using A -> B C D E. On the next step, ...
0
votes
1answer
105 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 ...
0
votes
1answer
48 views

Scrapy data analyzed with R, mongoDB or other pipeline? [closed]

I'm scraping data using the Scrapy library in python and i'm planning to make few analysis in the future using R. In the future it could be a very big database with millions of items, what are the ...
3
votes
5answers
979 views

Database: One table or many

I am working on a project of classified ads site. I have 12 main categories, and their items are 142, Like vehicles have cars, scooters, bikes, etc. Mobiles Phones have mobiles, tablets, accessories. ...
2
votes
2answers
93 views

What are the prime and non-prime attributes in this relation?

R(HIJKLMNO) L -> MNO HI -> JKLMNO J -> KL K -> H HI is a key. So, H is a prime attribute. But, H is also in non-key part. What are the prime and non-prime attributes in this ...
0
votes
1answer
54 views

sqlite database table with 1000's of columns

I am using sqlite3 in an application to conduct an alalysis of distances between sets of points. Distances between these points have been precomputed using dijkstra's algorithim and left me with a ...
1
vote
1answer
78 views

Should I look into alternatives to a relational database? [closed]

I am currently in the process of going through some PHP training with procedural code and will be doing a course in OOP with PHP right after that. The course will provide a solid starting point for me ...
11
votes
2answers
319 views

Implementation of a many-to-many relationship with total participation constraints in SQL

How should I implement in SQL the scenario depicted in the following Entity-Relationship diagram? As it is shown, every A entity type occurrence must be related to at least one B counterpart ...
3
votes
2answers
83 views

MySQL - Order by closest value on multiple columns

I have a table for a game lobby. In this lobby a user gets put in their connection and their level. In my SQL statement I am getting all the users in the lobby which are between +5/-5 of the user's ...
1
vote
1answer
81 views

Efficient way to store multi choice in a column with few options

I am looking for advices on how to handle multiple choices in a column? By way of example lets say selecting a fruit for lunch. Options are Apple or Pear. And you are 100% sure those two options ...