Relational database design to repeat data in a few columns

Here's the kind of data I am working with: There is user data like email id, age etc: And there is app specific settings data (opacity, speed, volume etc) for which there are default values but user is free to change the defaults and customize the ap

Algebra Relational sql GROUP BY SORT BY ORDER BY

I wanted to know what is the equivalent in GROUP BY, SORT BY and ORDER BY in algebra relational ?Neither is possible in relational algebra but people have been creating some "extensions" for these operations (Note: in the original text, part of

How to select equivalent but inverted tuples in mySQL

I have a table with 2 columns, filled with numeric values: Column1 Column2 1 2 6 9 2 1 9 6 1 3 Tuple (x,y) is equivalent to tuple (y,x) in the domain of my problem, and i need to perform a select where i get only one of them. Meaning, i would like a

Retrieving datasets from a cross-linked table in a single query

Imagine the following MySQL table environment: admins id username usergroups id groupName adminXusergroup (crosslinking table) id adminId groupId adminId and groupID are indexed and foreign key relations are configured accordingly (adminId points to

Many (many) SQL JOINs vs multiple queries

I'm here to ask a question that many of you have already ask yourselves, I suppose. I am creating a PHP website, and everything has been running smoothly until I decided to populate my database with some test data (real data, which when the applicati

When can an SQL result contain duplicates?

I'm just learning about relational databases and was told they don't allow duplicate entries. I did not believe this at first because I wrote SQL queries which returned results with duplicates. It's that the system doesn't store sets that are identic

Aggregate XMLTYPE values ​​in the database

I have XML data stored in each row of field in XMLTYPE, the first row of the XML field should be: <cdata> <r> <year>2009</year> <month>Jan</month> <day>1</day> <data>1180</data> </r> </c

Simulate relational data (or a database) in Python?

I often use Python to prototype ideas for other platforms, a scratch pad of sorts. Right now I want to play with some ideas related to relational data in a database. What is the best way to represent a database in Python? A set of tuples? Using a mod

Multi Map / Reduce the index relative to the relational seam

One of the key factors making NoSQL databases more scalable is absense of join operation which is essential part of RDBMS. But at the same time, for example, RavenDb contains Multi Maps / Reduce index feature which looks similar to join. Why do joins

history table and 2nf

Users table and Group users table are history tables (they cannot be moved to archived tables cause that would mess up foreign keys related to those tables. If a group user made a post and left the group, the post should still exist etc) Users Userna

Best point to start developing a database interface in C ++?

I'm working on some server/client based application at the moment. The application is multi-threaded, has plugin-support (via dlopen,...) and should work with multiple databases. Now I'm looking for some hints to start the design of the DBI-ABC. Firs

Migration of data between different DBMSs

As i couldnt get any satisfying answer to my Question it seems we have to write our own program for that, we are in the design phase and we are thinking which format shall we use to backup the data. The program will be written in Delphi. Needed is Ex

SQL, help with ORDER BY

Given: SELECT projectID, urlID, COUNT(1) AS totalClicks, projectPage, (SELECT COUNT(1) FROM tblStatSessionRoutes, tblStatSessions WHERE tblStatSessionRoutes.statSessionID = tblStatSessions.ID AND tblStatSessions.projectID = tblAdClicks.projectID AND

The best way for Unique Random String for MySQL long table

I know how to create random chars both at PHP and MySQL but the question is that I have to create a 4 char random string for a table of 10 thousand or so rows. What way is the best to make sure it will remain unique? I can use a longer string if I ne

Relational Database Design - Two 1: 1 or 1: 2 Relationships?

This question is about how to design a SQL relationship. I am pretty newbie in this matter and I'd like to know the answers of (way) more experts guys... I am currently migrating a ZopeDB (Object oriented) database to MySQL (relational) using MeGrok

Non-zero foreign keys as standard database

Background: today, an issue arose while using SQL Server Reporting Services. It seems that SSRS drop-down parameters in the report viewer don't allow you to indicate a (null) option so that you can see a report where that parameter is null. Basically