Questions tagged [database-design]

1

votes
1

answer
78

Views

MongoDB one-to-many - array of IDs or ID ref on child model? [closed]

Specifically, I'm thinking of a chat app, where a User has many Chats and each Chat has many Messages. How would I express the Chat --has-Many--> Message relationship? Initially, I thought I'd store the list of Message ID references on Chat, e.g. a Chat document could look something like { _id: Obje...
Code Jockey
1

votes
2

answer
51

Views

How to search in array with LIKE operator

id | name | ipAddress ----+----------+------------------------- 1 | testname | {192.168.1.60,192.168.1.65} I want to search ipAddress with LIKE. I tried: {'$mac_ip_addresses.ip_address$': { [OP.contains]: [searchItem]}}, This one also: {'$mac_ip_addresses.ip_address$': { [OP....
YOGESH MISHRA
1

votes
3

answer
92

Views

How to enforce by constraint that a referenced row also has a given type

I have multiple tables B,C,D ... referencing one another table A. Let's just look at A and B: CREATE TABLE A ( ID int PRIMARY KEY , TYPE enum_type ); CREATE TABLE B ( A_ID int REFERENCES A(ID) ); Is it possible to check that A has always one specific TYPE when referenced by table B? every row in A r...
Cyborg-X1
1

votes
3

answer
1.4k

Views

Product Information Management Solution or how to write my own [closed]

I am developing a website for a manufacturing company. The company has a few thousand part numbers. The parts fall into around 150 categories. Each product category has specifications (length, height, width, etc). I need to find a way to store all of this in my database for the website. The pr...
Ben Hoffman
1

votes
0

answer
24

Views

What is a better way to manage same database tables for multiple products?

I have multiple products, like Google has drive, docs etc. One common Auth server is there which will contain users table. But I have roles table related to each product, which contains roles for user for respective product. Now how to manage this roles table? I have following ways to do this, which...
ATUL SHARMA
1

votes
3

answer
53

Views

Should you store user login details in the same database as your website?

Basically, I want to create a restricted HTML front end to present and edit fields in a database. Users will have to log in with a username and password to gain access. Would you store the user login details in the same database but in a separate table, or in an entirely different database?
Col M
1

votes
4

answer
52

Views

SQL - nested joins

I have a mySQL database in which I'm trying to output a list of users and their assigned, administrative roles. My tables look something like: Users: ------- - id - fname - lname Role_Names ----------- - rn_id - role_name Roles --------- - role_id - user_id here's some data: Users: ------- 1 'Chris...
Daveh0
0

votes
0

answer
23

Views

Is there any way I can sort a this table using 'ORDER BY'?

I have an 'accounts' table that contains a group account (isgroup) with subaccount (groupcode): | code | title | groupcode | isgroup | groupindex | |------|----------------|-----------|---------|------------| | 100 | Current Assets | | 1 | 0 | | 101 | Cash...
Melvin
0

votes
1

answer
30

Views

database design question - null foreign keys

This is a trucking app that is tracking expenses. I am a noob on the back-end and still learning database design. I want to be able to filter it in the front-end by either the type of expense(truck,trailer,general) and also by the individual truck or trailer So here I would want to be able to just v...
Rigo De La Torre
2

votes
2

answer
101

Views

Recipe Database Design

I am trying to create a database to store my recipes. However, I am not sure how to implement it. I looked at other questions like this but they do not have the same focus as I. I assume any dish is actually just an ingredient, which can then be used in other dishes, or in this case in other ingredi...
Halhex
0

votes
0

answer
9

Views

Selecting a Database Type to hold Product Information Data

I'm working on a database design project where we need to store product information data and make it accessible through a REST API. The end goal is make the product data available to multiple companies who intend to use the product data on multiple, varying e-commerce platforms. The data store wher...
Gary Gerson
1

votes
1

answer
47

Views

Modeling database to support complex company-product-price relationship

In software that we are building we have a parent-child relationship between companies (one company can only have one parent company but can have multiple child companies) and we need to adjust our product pricing for every company. Here is a quick db model of how it should look like but this Compan...
kljuco
1

votes
0

answer
76

Views

Which database schema should i use?

I am building a Rest Api using node, MySQL and MongoDB, but i am confused with the database schema to go for as the business case is B2B and for each business(customer) there is like 10 tables for general ledger, products, transactions, clients, sales, purchase and many like these. and for accommoda...
Suraj Malviya
1

votes
0

answer
60

Views

Django Model Design (Big model vs multiple)

I have a question about designing my models. Suppose I have a following model: class Comment(models.Model): who = models.ForeignKey(User, on_delete=models.CASCADE) created = models.DateTimeField(auto_now_add=True) text = models.CharField(max_length=1000) likes = models.IntegerField(default=0) parent...
Meemaw
1

votes
0

answer
310

Views

MongoDB - Problems with database design and statistical data calculation

I am currently facing a problem with MongoDB and that's probably due to my schema design, but I am unsure how to go about it. So basically I have this collection (mongoose Schema): const PlayerStatsCollection = { sportType: MongoId, player: MongoId, match: MongoId, team: MongoId, date: Date, league:...
Morphex
1

votes
0

answer
64

Views

Crud opeartion in oracle tables with temporal nature

I am implementing api to insert, update and delete data in oracle table. Table has valid_from and Valid_to (temporal nature) columns. There will be no hard delete in the table. Records will only be inserted in case of save/update/delete. only valid_to date will get populated every time. I have 2 tab...
user3176053
1

votes
0

answer
28

Views

How to model users in the system?

I am working on system that will manage orders. Orders can be created via admin or via customer. Employee can take orders and change it's status. My system should have three global types of users: Admin - this type of users mostly uses WEB interface. Admins have different access levels, so some of t...
Aleksey Dolgiy
1

votes
0

answer
213

Views

Database schema for timesheet and roster

Can someone guide me with a rough database scheme for timesheet + roster. The idea in this database is to: 1) Store the weeks shift for employees, as there are 3 different shift 2) Allow employees to apply time off from work due to personal reasons. 3) Find out total workings per calender day in o...
envals
1

votes
0

answer
189

Views

DBLinks or REST API to read data from 2 different databases, in an application?

I have two different Java-based web applications each having two different oracle 12C databases respectively. Both applications consume data via SPRING framework based DAO layers from respective DBs. Both the databases have 2 tables called EMP_1 and EMP_2 respectively wherein they have some common f...
user1697113
1

votes
0

answer
81

Views

How to handle API with a big json file?

Let's assume I have a rather big (100mb - 250k json objects of similar schema) json file with data. My goal is to store this data on a server and provide API calls for it. The problem is that if I dump it into RAM, it takes more than 1GB of memory, which is obviously too much. The question is - What...
Maxim Maliarov
1

votes
0

answer
56

Views

Does the relational schema with some “aggregation” attributes satisfy Boyce–Codd normal form (BCNF)?

Suppose a relational database has employee and company entity with workFor relation, such as employee(ssn, first_name, last_name), where ssn is the key. company(company_name, location), where company_name is the key. workFor(ssn, company_name), where ssn and company_name are foreign keys. Suppose th...
jaedong
1

votes
1

answer
64

Views

Modelling database with restriction on columns

I am trying to model the following with a MySQL database: A File can either go into a Box, or can go immediately into a Location. A Box can have many Files and will then be immediately placed into a Location. Locations can have many Files or Boxes. My Initial thoughts were to have two Foreign Key...
KittenKiller
1

votes
0

answer
136

Views

Design pattern - duplicate objects in Realm to generate static data

I’m a fresh starter with Realm Swift. I did some sort of Realm graph object (seems to work for most people) and a UI mockup to explain you what is the problem with my current design. An example of the UI workflow The user accesses a list of workouts he created and selects one. The user starts a t...
Carmelo
1

votes
0

answer
76

Views

DB schema for a resource-based messaging system

My laravel application has some services and products, for simplicity's sake, I will keep it as generic as possible and I am going to call an individual service/product a resource. I will have to implement a messaging system in such a way, so that a client can send message to the admin (and vice ver...
Tanmay
1

votes
1

answer
44

Views

Can I create my database design for my CMS like this?

I'm designing a standard CMS database structure. These are my entities tables: ServicesPage --ServicesPageId --HeaderTitle --HeaderParagraph AboutPage --AboutPageId --HeaderTitle --HeaderParagraph ContentColumns (A section that can belong to pages) --Id --Title --Paragraph --Image --FK_PageId ...an...
DigitalNinja
1

votes
1

answer
76

Views

how to get values as per mixed attribute column set for products in sql

I have been struggling to get the desired output from the mysql db. First Please take a look at my table structure EDIT: HERE IS MY SCHEMA http://rextester.com/edit/NNCZ7986 Now when i run the below mentioned query, i get the results as below Select a.prodid,a.product,b.catid,b.category,d.attvalue,...
Neil
1

votes
1

answer
695

Views

MYSQL Error - Duplicate key on write or update

CREATE TABLE IF NOT EXISTS `demare`.`shop` ( `shop_id` INT NOT NULL AUTO_INCREMENT, `shop_image` VARCHAR(255) NOT NULL, `shop_price` FLOAT(6,2) NOT NULL, `cart_id` INT NOT NULL, PRIMARY KEY (`shop_id`), INDEX `cart_id_idx` (`cart_id` ASC), CONSTRAINT `cart_id` FOREIGN KEY (`cart_id`) REFERENCES `dem...
V. Michelle
1

votes
1

answer
36

Views

Designing a posts database

I need to create a database for an app which shows posts by different authors. Some of the necessary columns are: id (INT) url (TEXT) content (TEXT) date (TEXT) author_username (TEXT) author_fullname (TEXT) author_avatar_url (TEXT) author_url (TEXT) Now, each post by an author can be from different...
jL4
1

votes
0

answer
71

Views

how to solve this database circular dependency

I'm creating this simple database which has some sort of tasks created by a company and assigned them to employees. company has many employees company has many tasks employees have many tasks In the end, I see all the four tables are in a circle. I'm not sure whether it is good or not. How can I avo...
jonarya
1

votes
1

answer
31

Views

Cassandra: data modelling groups

Im seeking advice how to design the following usecase: User can be part of an Unit. If he is part of an unit and creates a device it should be visible to all users of the unit. If he is not part of a unit only he should see his own devices. Im thinking about storing devices in 2 tables: a) device_by...
Alex Tbk
1

votes
0

answer
31

Views

Advice for PostgreSQL Options with Common Notes Functionality between three options

We are doing a new design for notes We have three options for other database tables (persons, stores, items) to each have notes associated with them: No Association Table The notes table has separate columns for person_id, store_id, item_id. Only one of the three can be non-null. Benefits More effi...
Ask613
1

votes
1

answer
93

Views

Database Schema for group

I have a simple user and group database. User: uid(pk), name, password ( don't worry about password in plain) Group: gid(pk), name, owner_uid Member: uid (fk), gid (fk) ( a user can belong to multiple group) Now, I do not trust my database administrator and want to encrypt/secure specific coloumns...
Roshan Mehta
1

votes
3

answer
65

Views

MySQL Design: Seperate Schema or Table Prefixes?

I have a hobby project where I am creating my own software that will have different modules. The idea is to make the modules stackable and can be toggled off and on. I am using MySQL as the database back-end, and am wondering if it is better to have module-specific tables on their own schema, or i...
Bad Programmer
1

votes
1

answer
31

Views

When an object can belong to several other objects, where does the foreign key belong?

Using Django 1.11.6 and Postgres 9.2 If I have the following models: class RoughDraft(models.Model) class Manuscript(models.Model): rough_draft = models.ForeignKey(RoughDraft) class AudioBook(models.Model): manuscript = models.ForeignKey(Manuscript) class Series(models.Model): class Book(models.Mode...
Brian Witte
1

votes
0

answer
56

Views

What are Pros and Cons in using prefixes and suffixes in PostgreSQL dialect for timestamp columns

I have analysed several articles about naming conventions for Date/Time types in SQL data models. Most of them suggest implementing a database design where a timestamp type is used for some registered even values only, literally timestamping the event case just when it happens. And naturally they su...
KoichiSenada
1

votes
1

answer
30

Views

Rails architecture account has many accounts

I need to implement following architecture for forex trading(pamm accounts) where account(pamm account) has many accounts(other trading accounts) I think i need to add new entity for compose accounts. am i right? something like this? class Investment < ActiveRecord has_and_belongs_to_many :accounts...
sibukejo
1

votes
0

answer
39

Views

Oracle (ORA-02270) : no matching unique or primary key for this column-list error: when referencing a materialized view

I am getting the ORA-2270 error when I try to create some Oracle tables. The error is quite simple from what I have read so far: It happens when the columns I reference in the foreign key do not match a primary key or unique constraint on the parent table. The problem is that my tables have severa...
andcl
1

votes
0

answer
47

Views

Building architecture on top of abstraction

I am developing a .NET Core Web API project and experiencing the following issue: I have a simple domain of one base class and three derived classes. For simplicity let me name them in the following manner - Design1, Design2, Design3 and the abstract class Design public abstract class Design { } pub...
Georgi Karavasilev
1

votes
1

answer
345

Views

Quiz database design

Hi i'm working on a quiz system and i'm stuck on the last table user_quiz on how to link it to other tables? I want it to keep track of the score the user got on the quiz and also the total number of quizzes that the user has taken. The userid is the primary key, but im stuck on what the foreign ke...
Zestyy99
1

votes
1

answer
25

Views

Look for matching and unmatching fields on a same table in Access

I'm having some trouble with Access. I have a PHONES table with two very specific columns: Provider_prefix and client_prefix. I need to be able to generate a query that will give me every provider_prefix that is not on client_prefix (that is, there are no clients in the prefix area of that provider)...
JP Barroso

View additional questions