FAQ Database Discussion Community


mySQL Java Database Modeling

java,mysql,database-design,multidimensional-array,arraylist
In trying to create a SQL database that can be queried within java, but I'm not sure how to structure it. Here are my categories: 1. State 2. Cites within each state 3. Venues within each city 4. Specifics about each venue This might seem simple to some, but I...

Rails 4: Complex Many to Many Relationships

ruby-on-rails,activerecord,database-design,model,associations
I have the following models.. Provider Patient Clinic Like an eco-system, all models should have many-to-many relationships with one another. It's very important that I am able to query data from all directions. After intensive research on Active Record associations, I find many blogs warning against has_and_belongs_to_many and using has_many...

Functional dependencies for a relation with all prime attributes?

database,database-design,relational-database,database-normalization,functional-dependencies
I am doing a database theory course as part of my CS degree, and we have to confirm a database we designed is in BCNF normal form. I am currently trying to create the functional dependencies, but i am unsure of what to do when a relation is all prime...

What is a good way to manage large ever growing tables in a database?

sql,database-design
I am building a web application for medical record keeping. A requirement for this application is logging all changes (view, create, update, delete) to a patients data and pretty much any other useful info in the system (login, cron run, data export, etc). I am storing the data into a...

Observe age of entries in the database and trigger event

mysql,database-design,triggers
I'm wondering if there's a way to trigger an event into a database based on the age of the row entry. Based in a datetime field I will need to retrieve the entrys that match an exact age, but I don't want to stress the database. So I need the...

Replicating tables within the database

java,database,database-design,data
I have table A, B and C with data that is similar, but with different structure. I need to access the data from my program (Java, in this case) and I'm thinking of having a mini-ETL process that will run daily replicating data from these three tables into a new...

OVERLAPS operator to check multiples rows

postgresql,database-design,plpgsql,overlap
Currently I am using the overlaps operator so that users can't insert dates that overlap. I created this function: CREATE OR REPLACE FUNCTION overlap(docent_medewerkerscode1 CHARACTER(7), lokaal1 TEXT, groep_groepsnaam1 TEXT, startdatum1 timestamp, einddatum1 timestamp) RETURNS TEXT AS $$ DECLARE resultaat TEXT; rec rooster%rowtype; BEGIN FOR rec IN SELECT groep_groepsnaam = groep_groepsnaam1...

Why sort_order in a table when we can sort it by using keyword ORDER BY

mysql,database,database-design
I was going through a database (learning database) and found sort_order in a few table. What could be the purpose sort_order in this table?. If it (as I think) is for sorting, we could use "ORDER BY" in the query for sorting. ...

Database Designing One-to-One Relationship

php,mysql,database,oracle,database-design
I am a beginner in database design. I am a little bit confused about a one-to-one relationship. I have a table called task_tbl and I want the user to rate a task (the rating will be out of 5, 1 to 5 stars). I am a little bit nervous about...

Handling missing values in a database schema?

database-design,modeling,missing-data,representation
Suppose I have the following table schema MONTH VARCHAR(10) HASRAIN BOOLEAN YEAR INTEGER Now suppose for a particular year, I have values for 11 months, but no value for the the left over month (let's call it September). Is it more correct to: Put a record in with a zero...

ER diagram for booking database

database,database-design
I'm creating a database for booking apartments app. I have a question - it is allowable to keep user's passwords in DB (for example using BCrytp)? Another question - where is the best way to keep price if it may vary during several months?

BCNF Decomposition?

database,database-design,relational-database,database-schema,bcnf
Given relation R(A,B,C,D,E) and FDs = {A->BC, CD->E, B->D, E->A} While converting R into BCNF: ABCDE -> BD & ABCE will be first decomposition BD is in BCNF Regarding ABCE: Opinion 1: ABCE has FDs: A->BCE, E->ABC, BC->AE Therefore its already in BCNF Opinion 2 ABCE has FDs: A->BC, E->ABC...

Anchor Modeling - are data types part of the Model?

database-design,data-warehouse,temporal-database,6nf,anchor-modeling
A question about data types in the Anchor Model database design. The question assume separation of anchor model implementation from the anchor model itself. In the Anchor Model xml we have following kind information related to data types: dataRange="varchar(42)" identity="int" timeRange="datetime" They are stored in Anchor Model entities (anchor/attribute) xml...

Query genre and sub genre of authors and books

database,oracle,database-design
I want to create a DB for books. The DB should include the tables shown bellow. suppose i wanted to write a query that returned authors.first_name, author.last_name, works.title of authors that wrote haiku poetry; where poetry is the genre and haiku is the sub genre. I really have no idea...

Rails ActiveRecord getter override

ruby-on-rails,activerecord,model-view-controller,database-design
So I have a model called Primer which has_many :amplicons. However, in my DB the Amplicons table stores the primer id in either a forward_primer_id or reverse_primer_id column (i.e. not the default primer_id convension). In the Primer model I would like to direct Rails to search the Amplicons table in...

PostgreSQL multiple nullable columns in unique constraint

sql,postgresql,database-design,null,unique-constraint
We have a legacy database schema that has some interesting design decisions. Until recently, we have only supported Oracle and SQL Server, but we are trying to add support for PostgreSQL, which has brought up an interesting problem. I have searched Stack Overflow and the rest of the internet and...

Save Followers count in a field or query each time if needed [closed]

database,mongodb,database-design,mongoose,software-engineering
I want to create an App like Twitter. Now i have a question about this projects database architecture. I want to show each users Followers/Following count in his/her profile like Twitter, but i don't know that i have to query every time from Followers/Followings table/collection or this values can be...

What would be the correct ERD design approach?

sql,database-design,erd
Teams can have 1 or more members Members can join 1 or more teams A member can lead 0 or more teams. Each team has a single member leader, where that team leader must be a member of that team. (eg. If a team had only 1 member, that...

How do I store guest checkouts?

php,mysql,database-design
Ok, so my site is just like "Post Your Order" rather than the usual online payment method since I will be shipping the item first then the cash which is the Cash On Delivery offered by Shipping services here. I have a users table which have user_id, email, password, first...

to alter or create a new table in cassandra to add new columns

database-design,cassandra,datastax,datastax-enterprise
I am using DSE cassandra. I wanted to add new attributes to the existing table. I wanted to know what is the best practice to achieve this? Should i be adding new columns to existing table or creating new table? What are the pros and cons for either approach?...

Select sum or updating a field for Total Balance?

sql,database,entity-framework,database-design,sql-azure
I'm using Entity Framework and Azure Sql. I have users and they have records on balance table.Some of users may have 1 million record.I need total balance of the user before every http requests. I have two approaches for getting total balance of user: First: Insert balance and update totalbalance...

Inventory Database: Merging Perpetual and Periodic inventory system

mysql,vb.net,database-design
My client ask me to create an inventory system that can of course print report daily, weekly, monthly and yearly. So far, with this description I am able to do the system and the output will be something like this: ------------------------------- |id|cost|price|quantity|profit| ------------------------------- |1 | 10 | 12 | 100...

Database Design - How much logic via foreign keys?

database-design
This is a theoretical question about database design. I could "abuse" relations as conditions in the database or I could use conventional if-conditions in the source-code. I'll try to explain my question with a example: Let's pretend we have two acting characters (pseudocode): TABLE Human( int humanId PK, string name...

Database Design: How should I store user's news preferences in MySQL database?

mysql,database,database-design,relational-database
I'm trying to implement a personalized news aggregator. I have to save user's preferred news sources in db. Should I store all the news sources liked by a user as JSON string and then decode it after retrieving? feeds | user or have individual column for each news source(no of...

Best database & schema for a tree?

mysql,database,mongodb,database-design
What database (and schema, if applicable) would be most appropriate for storing and retrieving data (location, timestamp) that can be placed at any node of an arbitrarily defined tree? For instance: the location of a book you own: Book | | Home Work | | | | | Bedroom Bathroom...

Database for image tagging

c#,database-design,linq-to-entities
I need to design an image tagging database for a school project. The site will have Images, Tags and Users. Evey Image gets tagged with one or more of the tags (Tags are things like: summer, beach, Tyoko etc.), but I also need to keep track of the history of...

When is oracle.net.ns.Packet.recieve() method invoked?

java,database,oracle,database-design,oracle11g
I have not been able to find documentation of oracle.net.ns.Packet.recieve() method anywehre. Is this method only invoked on select queries ? or is it also invoked on insert update etc. ? My application is spending majority of it's time in this method. What can I do to improve performance ?...

Why I can't join partial tables into one in Sqlite3?

database,database-design,sqlite3
I decomposed a large table into five tables based on BCNF and I tried to join them all together to check if I lost something or not. I used this sql statement in Sqlite3 Select spart.Sno, spart.Sname, cpart.Cno, CtoT.Cname, CtoT.Tno,tpart.Tname, scorepart.Degree from spart, cpart, CtoT, tpart, scorepart where cpart.Cname...

Messaging table performance - Merge from and to id`s into one single field vs separate 2 fields

mysql,performance,database-design,relational-database,database-schema
What would be the implications on performance on merging the users id from the "messages" table in form of "fromto" - ex userID - 5, userID 9 => "59" versus having two separate columns "from" and "to"? Flow - list, read, delete messages when both id`s will be available in...

Algorithm to track the price of a particular item every 5 minutes

php,mysql,sql,algorithm,database-design
I have a PHP script that fetches the price of a particular item from an API. The script is executed by a cron job that runs every 5 minutes. The price of the item remains same most of the time, and changes only about 4 or 5 times a day....

Having four different tables or having one table with one more field? [closed]

mysql,database,sqlite,optimization,database-design
I have a website with 4 translation (That is Arabic with 4 different Persian translation), and I had exported each of these translations to different tables. Which one of these ways are faster and better: 1- Merging or Importing all of this translation (tables) to one table, and name it...

C# Storing player cards Database Design

c#,mysql,sql,database,database-design
I'm having really hard time figuring out how to store cards each player owns in SQL database. My bets idea so far is to have one table with cards: [id], [cardName] And to associate it with players like that: [id], [playeName], [cardId], [quantity] This setup will allow me to easly...

Add files to multiple tables M:N

sql,database,database-design,foreign-key-relationship,multiple-tables
What is The best Data model for Add multiple files to The multiple tables? I have for example 5 tables articles, blogs, posts... and for each item I would like to store multiple files. Files table contains only filepaths (not physicaly files). Example: Im using The links table, but when...

MySQL Multiple tables or multiple values for single field?

php,mysql,database,database-design,relational-database
This is a problem I've had for a while and not sure what is the best way to solve it. Its probably easier for me to explain using an example. So I have many collections and many items. Some items can belong to one or more collections or none at...

Is this pattern suitable for Core Data?

ios,core-data,database-design
The only databases I've worked with before are MySQL so the database design of CoreData is confusing me a little bit. Briefly, the design consists of a many-to-many relationship between people and businesses. Many people can own one business. One person can own many businesses. In this simplified design, there...

Good practice for handling naturally JOINed results across an application

php,mysql,database,join,database-design
I'm working on an existing application that uses some JOIN statements to create "immutable" objects (i.e. the results are always JOINed to create a processable object - results from only one table will be meaningless). For example: SELECT r.*,u.user_username,u.user_pic FROM articles r INNER JOIN users u ON u.user_id=r.article_author WHERE ......

Rails rule of thumb: When to put things in 2 separate models/tables?

ruby-on-rails,ruby,database-design,model
When would you put data in multiple tables and when do you keep them in one and the same table? For example, what are the advantages and disadvantages of putting a system admin – who has exactly the same fields as another user type, except an additional Boolean for admin...

How to implement a one-to-many relationship with an “Is Current” requirement

sql,database,entity-framework,database-design,relational-database
Designing a database, there's a relationship between two tables, Job and Document. One Job can have multiple Documents, but one (and only one) of these Documents needs to be flagged as IsCurrent. This is not always the most recent Document associated with that Job. Structurally, I can see two ways...

Finding redundancy in table

database,database-design
I am having following database : Now I want to identify the major data redundancy problems exhibited by this file structure. Also are there some changes that can be made for the Employee_Name and Employee_Phone? I am new to database and all, so please help...

Which one should be preferred 1:1,0 or nullable fields on one table on relational database?

entity-framework,database-design,orm,ef-code-first,relational-database
I use Azure Sql and Entity-Framework Code First. I have Transaction Table. There are 4 types which are buy/sell, withdrawal, deposit and fee. For example, I need DepositCode for deposit transactions but this column will be null for others. Also I need ItemPrice and ItemAmount for buy/sell but it will...

Mysql Transaction: Effect on other Transaction query

php,mysql,sql,database-design,transactions
I am writing an application which shall track the financial transactions (as in a bank), to maintain the balance amount. I am using Denormalizing technique to keep the performance in check(and not have to calculate the balance at runtime) as discussed Here and Here. Now, I am facing a Race...

Database Design Advice for a Social Network App Needed

database,database-design,database-connection,relational-database,database-schema
I am very new into backend stuffs like databases. That being said, I lack the database design knowledge. I am wondering how and what is the best design for my scenario. I am creating a social network app where the users can create groups and join other groups. Those groups...

database Junction/association table with more data fields

sql,database-design,many-to-many
I am not a fan of having more than two fields (eg: pk1 & pk2) in a junction/association table. I do have a situation, however, and not sure what would be the best approach to resolve it. Table 1: Contract (id, price, description) Table 2: Person (id, firstname, lastname) Table...

Unique usernames in 2 tables at different levels. Suitable database design?

database-design,relational-database,database-schema
I have a table with suppliers. This table contains, among other things, name, username and password hash. Sometimes a supplier is just one person, in which case this works fine. But sometimes a supplier is a company and in that case, multiple employees may need to log in. Until now,...

MySQLi query to join over several tables with pure relationship tables

php,mysql,database,database-design,mysqli
so I have this schema as shown in the image. Now while I've been trying to figure out all day how to do this, I gotta say I haven't become a bit wiser.. What I want to know is if it is possible to get a result like this from...

Should I create another model for admins? Or what's the best way to do it in Ruby on Rails?

ruby-on-rails,database-design,architecture
I'm developing an app, which requires two types of users regular user and admin. Regular users have roles in the app, and an admin doesn't have any roles, he just should have an access to an admin panel. What's the best way to implement this task? ...

Aes-Gcm / Aes-Cbc - Database Storage - Column design

c#,database-design,cryptography
My question is about what I actually need to store in the DB re encrypted values and how this can be used for the nonce for GCM specifically. For reference these two answers provide sample code for encrypting data: Cbc and gcm. As I understand it, CBC requires the IV...

Create Trigger which updates a column automatically after update of a table

mysql,database,database-design,triggers,mysql-workbench
I have a table which looks like this: |idAvailability|SumRooms|AvailableRooms|AvailabilityRate| everything is int except AvailabilityRate which is float. I want to create a trigger which when updating the AvailableRooms column, automatically because of the existance of the trigger the column AvailabilityRate update as well. This is what I have done so...

Table structure for web shop

database-design
Please consider this scenario: A web shop sells IT products. If we look at computers and printers, than there are some filters that both products need: ID, name, price, e.d. Some filters are specific to only one product: OS, cartridge type. What is the best way to store these products?...

Relational database versus R/Python data frames

database,database-design,dataframes,data-processing,data-collection
I was exposed to the world of tables and data structures in R before the RDBMS systems and other database systems. It is quite elegant in R/Python to create tables and lists from stuctured data (.csv or other formats) and then do data manipulations programmatically. Last year, I attended a...

How to maintain author order in database of citations

database,sqlite,database-design,rdbms
I am writing a program to help authors maintain lists of references for their research. It must be possible to reconstruct citations in various styles from the database. In the database, I maintain a many-to-many relation between authors and the rest of the reference (title, year of publication, etc.) using...

foreign key constraints. can child table have null values? ERROR 1452 (23000)

mysql,database-design,null,foreign-keys
I have a controlled vocabulary table set up as such: -- create the film_stock table DROP TABLE IF EXISTS film_stock; CREATE TABLE film_stock ( stock varchar (10) NOT NULL, PRIMARY KEY(stock)) ENGINE = INNODB; There are only two values assigned to 'stock' - Nitrate and Acetate. 'stock' serves as a...

TABLE.label doesnt exist YII, framework

php,database,database-design,yii,localhost
I was working with YII framework, I made the models and then I used the Crud generator and I also made those things using Crud generator. Everything works fine so when I go to localHost/myprogram/index.php/myTable I can see the table myTable. I have 6 tables in my Database and they...

Preventing Multiple Votes from a single user, with anonymity

android,database,amazon-web-services,database-design
I know this is a vague question, but I suppose I am unsure of where to begin my research. I have an android application that allows users to post information to a database, via a java web server that I have code on. Users are also allowed to up/down vote...

Best practices for creating a huge SQL table

mysql,database,database-design,coding-style,large-data
I want to create a table about "users" for each of the 50 states. Each state has about 2GB worth of data. Which option sounds better? Create one table called "users" that will be 100GB large OR Create 50 separate tables called "users_{state}", each which will be 2GB large I'm...

Should I normalize or not? If yes how?

mysql,database-design,database-optimization,table-structure
Currently I have a table with a column containing CSVs. I am not sure whether to normalize the whole table or not. The problem is this column, configuration, may contain up to 50 or more different types of values. For example in the table shown below it's 18, 20, but...

Calculations between 2 records of 2 tables need to extend to more then 1300 fields. How and which database can achieve this?

database,postgresql,database-design
For one of our projects we are using PostgreSQL and access two tables with complex SQL calculations due to performance advantages. Each table, lets call them TableA and TableB, have 1301 fields of type double precision (field number restriction by PostgreSQL!) The field names for TableA are: id, A1, A2,...

How can I store a backgammon board in MongoDB?

mongodb,database-design,nosql
I am new to NoSQL databases, and even though I've looked for it a lot, I cannot find best practices for this sort of thing. Backgammon, for those of you who don't know, is a game with a board that has twenty four pips (or slots), and a bar which...

Neo4j Design: When to use Properties for Relationships

database-design,neo4j
What is the proper case for using the Property attribute of a Relationship in Neo4j? (Including examples of when and when not to use them) Example Consider a computer used by Team A and Team B, where each team have their own internal value id for the computer: Node =...

How to represent a table with several one-to-many relationships

sql,sql-server,database-design,sql-server-2012,entity-framework-5
I have a table, A, whose rows have a many-to-one relationship with several other tables. The rows in the other tables--B, C, D--each have many As. Each A relates exclusively to another table. For example you never have an A that relates to both a B and C. Currently, the...

best temp table strategy for update/insert operation

sql,performance,postgresql,database-design,rails-postgresql
I'm given a list of transaction records from a remote server, some of which already exist in our database and some of which are new. My task is to update the ones that already exist and insert the ones that don't. Assume the transactions have remote IDs that aren't dependent...

Is it practical to load user settings/preferences from an embedded database? [closed]

java,database-design,settings
The program that I am currently developing will have user preferences and I would like to ask if it would be practical to load these settings from an embedded database? Like for example if the user wants a different color scheme, the value of that certain color will be stored...

Firebase data structuring - accessing jobIDs for each user

javascript,database,database-design,firebase
In my database, I currently have two kinds of objects, users and jobs. I am already storing userIDs in jobs. Do I also need to store jobIDs in each user? A typical user: "-JqzUjcOfddBNd_HtjKb" : { "contact" : { "-JqzWcIyD77ZwatEKALp" : { "email" : "[email protected]" }, "-JqzWrtyni3ZGOKooNF7" : { "email"...

Which column type for storing the year field in a table with rows of yearly data

mysql,database,laravel,database-design
My Laravel web app uses the schema builder for database portability, so the MySQL-specific YEAR column type is not available. I want to be able to sort and select rows of data by year (including BETWEEN). What's the best portable column type for storing year values?...

Choosing the right database index type

database,mongodb,database-design
I have a very simple Mongo database for a personal nodejs project. It's basically just records of registered users. My most important field is an alpha-numeric string (let's call it user_id and assume it can't be only numeric) of about 15 to 20 characters. Now the most important operation is...

How to set up Primary Keys in a Relation?

database,database-design,relational-database,database-schema,entity-relationship
I wish to know how to correctly set up Primary Keys in a Relation. E.g. we have ER-diagram which contain elements: Key attributes Weak key attributes Identifying relationships Associative entities In order to translate it into Relational Model we should do some tricks. All elements above deal with Primary Keys...

How do I select users that have not had their score updated within the last 2 hours or at all using Mysql

php,mysql,sql,database,database-design
I have the following 2 tables... users user_id, name, age user_score score_id, user_id, score, date(timestamp) The user_score table should keep a log of all scores for all users updated every 2 hours. So I need to get a list of all user ids that have not had their score updated...

Database design for recurring events with exceptions

sql,database,database-design,calendar,recurring-events
I'm building a system that needs to store/manage different types of events. For simplicity, I will focus on designing a calendar (I'm building something slightly different, but calendar is a good analogy and it's easy to reason about). I'd like to hear about possible database/schema design ideas. Problem Description I...

Should I drive a stats list from a database view

android,ios,database,sqlite,database-design
I am working on a game that shows user stats: enemies killed, time in game, etc. It is an ordered list of results, driven from an sqlite db. I'm struggling with a design choice for how to show the stats to the user. The problem is best expressed by example....

Strategies to speed up access to databases when working with columns containing massive amounts of data (spatial columns, etc)

ruby-on-rails,ruby,postgresql,database-design,postgis
First things first, I am an amateur, self-taught ruby programmer who came of age as a novice engineer in the age of super-fast computers where program efficiency was not an issue in the early stages of my primary GIS software development project. This technical debt is starting to tax my...

How to design a database to represent data that changes in structure?

python,mysql,database,database-design
Working on designing a database to store and track the history of the contents of a web form. Normally this would not be a problem. The naive implementation being a history table that records form field contents along with a timestamp. Here's the issue: This form could change over time....

Relational data modeling for sub types

database-design,relational-database,data-modeling,subtype,relational-model
I am learning the Relational Model and data modeling. And I have some confusion in my mind regarding sub types. I know that data modeling is an iterative process and there are many different ways to model things. But I don't know how to choose between different options. Example Suppose...

Which column(s) for Primary Key(s)

database,database-design
Sample Data for Nuts and Bolts Thread Series. I'm trying to determine the Primary Key(s) for this data set. Here is a sample of a few valid combinations. size form tpi 1/4 UNC 20 1/4 UNF 28 1/4 UNEF 32 5/16 UNC 18 5/16 UN 20 5/16 UNF 24 5/16...

Organize table avoiding redundancy

database,database-design,logic,store,database-schema
I'm trying to create a database to manage autobus data CREATE TABLE Company( Company_Name VARCHAR(12), Tel INT, PRIMARY KEY(Company_Name) ); CREATE TABLE Line( ID_Line VARCHAR(3), NCompany_Name VARCHAR(12), Desc TEXT, PRIMARY KEY(ID_Line, Company_Name), FOREIGN KEY (Company_Name) REFERENCES Company(Company_Name) ); CREATE TABLE Stop( ID_Stop VARCHAR(3), geoLat FLOAT(10,6), geoLong FLOAT(10,6), PRIMARY KEY(ID_Stop) );...

Does it matter which table has the foreign key in a one to one relationship?

sql,database-design
I'm designing my first relational database, and it's only a few tables, but I'm curious what the rules regarding foreign keys are. In this specific case I have a have a table for a comment and a table for a reply to that comment. Should the foreign key be in...

Exclude email address from a list of excluded domains

postgresql,database-design
I have an email client that lists emails like you see in the gmail inbox. All the emails are store in a Postgres 9.3.5 database. Part of the functionality I want to implement is to allow users to block incoming emails from a list of domains, e.g. @spam.com, I have...

Storing multiple item settings in database

mysql,database-design,database-schema
I'm building a gallery. Each image is a row with name and src and order etc. I want to have 3 more options per image: Featured, Visible, Disabled. Should I have those as columns? Or have just 1 column settings and store a 3 binary numbers? For example: 111 =...

Firebase data structure with cyclic dependency

android,database-design,structure,firebase
I'm currently discovering Firebase and trying to evaluate it for further projects. At the moment, i'm testing with a very simple "Todo list" app where lists can be shared with friends. I've read the Doc about flattening data structure to avoid complexity and I came up with the following structure:...

Table design for questionaire

sql,sql-server,sql-server-2008,database-design
We are working on an application which will ask users various questions, these questions can have branches, so they don't run in a straight order. for example, if the below is a structure of questions and where they can flow to: Question 2 has a branch if the user answers...

One to One ER with a Type or Status table instead of a Type or Status column

database,database-design,relational-database,entity-relationship
I have a long way to go with ER modelling, as you'll soon see, but I have a project I'm doing for a construction firm and I'm getting my hands wet with the database model. The company is a roof inspections company and they have projects on sites and proposals...

How to save user's custom input with a list of predetermined item

mysql,sql,database-design
I have a database which contain a list of items (item) +----+-----------------------+-------+--------+-------+ | id | name | width | height | depth | +----+-----------------------+-------+--------+-------+ | 1 | Some_toy | 35 | 28 | 30 | | 2 | Pepperoni_pizza | 17 | 30 | 35 | | 3 | Wood_table...

normalization of database structure

mysql,database,database-design,database-schema
I was reading the concept of normalization of database structure. I got confused with the following situation in my project. I have two tables "TableA" and TableB Both tables are independent of each other and have no realtionship at all They represent completely different data Both the tables will have...

How to add new relations and making weight dependent in MySQL database?

mysql,database,database-design,relational-database,database-schema
I am working on a health app and I'm designing a database (MySQL), which should store symptoms and diseases and bodyparts. The app will work as follows: User chooses gender, age and bodypart (front/back) App shows all (common/less common) symptoms of chosen bodypart User chooses symptom App shows all (common/less...

Database structure for storing personal skills

database,database-design
I need to design a database for storing skills for a person, a person can have none,one or several skills, what is a good way to store it when it comes to easy modification of skill and fast search? I have been thinking 1. use a bit array, each bit...

Surrogate Keys complicate insertion?

database-design,foreign-keys,key,relational-database,surrogate-key
I often see people using artifical / surrogate keys in relational databases. Thinking about it, it seems to me that while this simplifies join-Queries, it complicates the insertion of new tuples. Take the following example: R1(a, b, c) R2(c, d, e) c is a surrogate primary key of R2, referenced...

Student course database design

ms-access,database-design
i want to create a database design for computer institute where we store student record with their previous academic and non academic course which he took in past. student can be enrolled in more than one course in our institute and that course price can be changed in certain duration....

Cassandra data model to store embedded documents

mongodb,database-design,cassandra
In mongodb we can able to store embedded documents into a collection.Then, How do we store embedded documents into cassandra??? For this sample JSON representation??? UserProfile = { name: "user profile", Dave Jones: { email: {name: "email", value: "[email protected]", timestamp: 125555555}, userName: {name: "userName", value: "Dave", timestamp: 125555555} }, Paul...

Should I use a single table for many categorized rows?

mysql,sql,database,database-design
I want to implement some user event tracking in my website for statistics etc. I thought about creating a table called tracking_events that will contain the following fields: | id (int, primart) | | event_type (int) | | user_id (int) | | date_happened (timestamp)| this table will contain a large...

SET IDENTITY_INSERT ON/OFF needed on application server, but ALTER permission seems dangerous. Suggestion?

sql-server,database-design,web-applications,sql-server-2012
We are building a multi-user web app where they need an unique postId for post they create. Each post has (userId, postId) as the compound primary key. Right now, postId is an identity value, but because of the need to support some operations that require postId to be inserted as...

Storing entities with dynamic set of properties in a table (and using fixed columns or key-value tables)

sql,sql-server,database,database-design,key-value
I would like to store people's information in the database (table). Every person can have a different set of properties. I would like to store all these properties. But creating fixed number of columns does not make my application scalable. So, another approach is to store these values inside key-value...

Max number of columns and column length in Vertica?

database,database-design,data-modeling,vertica
We have some 3rd party marketing data that can take up to 3000 columns, and some of the column names have 300~400 characters. According to Vertica official docs(HP Vertica Analytics Platform Version 7.0.x Documentation), it says 1600 is the max. However, according to this article: http://www.dbms2.com/2012/07/28/some-vertica-6-features/, Vertica has effectively unlimited...

Unique hash/index for time interval

database,mongodb,database-design,hash
I am working on a simple resource booking app. The use of the resource is exclusive so it can't be booked more than once at the same time. I am wondering if this constraint can be enforced by a unique index instead of having to build validation in code. The...

How to add a N:M relationship to my MySQL symptoms and diseases database?

mysql,database,database-design,relational-database,relationship
I am working on a health app and I've created a database (MySQL), which stores symptoms and diseases. There should be a N:M relationship between the symptoms and diseases, so each symptom could have multiple diseases and vice versa. Symptoms should also have common and less common diseases and because...

T-SQL Recursive Function - CTE with ParentID

sql,tsql,recursion,database-design,common-table-expression
I am trying to write a query in SQL Server to extract all of the information linked to a Task but I've hit a bit of a problem. What I want to happen is when I pass in a parameter (@TaskID) and for that to return task information for that...

Is it OK to have DB columns for “display purposes”?

sql-server,database,database-design,web-applications
We have two tables that need fields to be displayed in the application in various pages/screens and on reports as CONCAT of separate fields (concat within the same table) 1) Customer-table: FirstName+' '+(first-character([MiddleName])+' ' if not-null)+LastName intended result: "John Doe" -or- "John M Doe" (Note: similar situation with AddrLine1+AddrLine2+City+State+Zip+Zip4) 2)...

Django: Asymmetric ManyToMany Relation

python,django,database-design
I am new to using django and would appreciate your input to the following problem. Two models are present, namely A and B. Instances of A must belong to at least one instance of B, while a B could contain any number of instances of As from 0 to infinite....

Should I put all data in one table for Implementing Filtration or use junction table

c#,sql,sql-server,database-design,filtering
I thought about using a junction table to avoid repeating the data in my database. So I have the following schema Apartments: +----+-------+----------+------------------+ | ID | Price | CityID | ContractTypeID | +----+-------+----------+------------------+ | 1 | 200 | 1 | 1 | | 2 | 150 | 1 | 2...

Difficulty in database table normalization (2NF)

database,postgresql,database-design
The following table design (see below for the full schema) leaves a lot to be desired and has caused many difficulties, but I can't figure out how best to normalize them. The purposes of the tables are: ICD9 — to provide a master look-up of CICD9 and CDESC combinations. Each...

Core Data multiple instances of same Entity - entities that share attributes

ios,entity-framework,core-data,database-design,data-modeling
I am trying to wrap my head around how to have multiple instances of the same Core Data entity. It does not seem possible, so I must be approaching this wrong. Basically, say I have a shopping cart that can be full of multiple balloons. But each balloon can have...

Django, multi-table inheritance is that bad?

django,database-design,model
This isn't really specific to django. One can model Place (with location, name, and other common attributes) - Restaurant (menu..) - ConcertHall (hall size..) in two separate tables and let each one hold all the fields they need. (in django world, this is called abstract inheritance) in three tables, where...