1
vote
5answers
91 views

Database design with performance issue and use of views

I am designing database for a school system. I am having to create a lot of tables and a lot of link tables to maintain a structure in the system. I am trying to maintain foreign integrity and trying ...
0
votes
0answers
38 views

Does this Relational Database Model make sense?

Here is a flat version of my datamodel. It is sensor readings for various data acquisition equipment. ...
1
vote
4answers
114 views

Are there any benefits in not having referential integrity keys in database?

So Referential integrity is good, but are there scenarios where not having constraints is better? For example when all the logic of relationships is kept in code? or when the database upgrade is ...
2
votes
1answer
82 views

How to define item level permissions in a relational database

I need to model a database design to provide item level permissions to users based on the user's role access. I am using asp.net with sql server 2012 I am looking at similar experience on how ...
2
votes
2answers
96 views

Should I be using a composite key for this table?

My database is going to store words, so the Word table is the most important. The Rhyme table lists which words a particular word perfectly rhymes with. It consists of three fields: RhymeID - primary ...
3
votes
4answers
728 views

Is this database design sound and fully normalised?

I'm creating a database to store words. The intended usage is to query the database to find a word matching a set of filters. E.g. if I wanted to find a word with < 10 letters, < 3 syllables, ...
2
votes
1answer
120 views

When deciding a Primary Key, can I use Date part of a DateTime field

I'm designing a payroll system I have designed a database with several tables and this question is related to following tables EMPLOYEE (Employee_ID [pk], Name,... etc) LOCATION (Location_ID [pk], ...
0
votes
2answers
103 views

Should all foreign table references use foreign key constraints

Closely related to: Foreign key restrictions -> yes or no? I asked a question on SO and it led me to ask this here. If I'm faced with a choice of having a circular reference or just not enforcing ...
1
vote
0answers
69 views

Redmine - Database Structure/Normalization

I am using redmine for project management and issue tracking. I was looking at the database tables and the underlying structure and was wondering if anyone who is experienced with database ...
1
vote
3answers
68 views

What are the use cases for these relationship types?

Use Case What are the use cases for creating a one-to-one relationship where the parent points to the child compared to a child pointing to the parent? If there is no difference, is one preferred ...
0
votes
1answer
107 views

Database design grouping many tables with their common fields [closed]

Is this a valid DB design? A user can create an organization, link as many offices to it, have an inventories that host items linked to organization or office, have clients for his business etc. | ...
2
votes
1answer
113 views

Is it substandard if I have multiple tables in my database yet there's no relationship at all? [closed]

I have multiple tables in my database. Some tables have relationship to others and some don't have. My friend told me that it is not advisable to have a table that is not related to others. Is it ...
1
vote
1answer
42 views

How to represent one special related record

I have two tables, project and photo. photo has ID,projectID,path : each project has multiple photos. Now one of these photos is the one that has to be displayed. The question is how to represent ...
2
votes
2answers
1k views

single table w/ extra columns vs multiple tables which duplicate schema

I am working on a project where at some point, I needed to make a decision on whether or not, in the database, I should have a single table with multiple columns that not every record uses, or ...
2
votes
3answers
215 views

Tracking correct order details in commerce database when modifying 'products'

I am implementing an ecommerce database. This is slightly different than most as the 'products' that are for sale are for services provided. For example a user (vendor) of the system may define a ...
0
votes
1answer
87 views

Relationships in a Chen ERD

I am working on a Chen ERD to model our organizations merchandise. Our central entity is a Style. We have supplemental entities of Color and Season. I am defining our assortment as the ...
2
votes
3answers
329 views

db design: circular references for the-one-of-many case

There are many questions about circular references around (if they are bad or not) but this one will be concrete. Consider the following case. There are two models Image and User. Users have ...
2
votes
2answers
101 views

How should this relationship be structured in a relational database?

In a recent project I was asked to implement an events system. An Event had to have a Location which was originally specced out as simply a physical location with some optional extra notes. Then the ...
2
votes
2answers
265 views

How should I make searching a relational database more efficient? [duplicate]

This is in the scope of a web application. I have a database which has a few nested relations. There is a feature which depicts the history of a large chain of relations. It is essentially a data ...
2
votes
2answers
783 views

Database model for keeping track of likes/shares/comments on blog posts over time

My goal is to keep track of the popular posts on different blog sites based on social network activity at any given time. The goal is not to simply get the most popular now, but instead find posts ...
0
votes
1answer
168 views

Best way to implement database design for a set of entities

Lets say I have a scenario like so: I have many claims to get money from another entity and this claim has to go through two gateways. This claim is sent from an origin system and can possibly be ...
15
votes
4answers
2k views

Why many designs ignore normalization in RDBMS?

I got to see many designs that normalization wasn't the first consideration in decision making phase. In many cases those designs included more than 30 columns, and the main approach was "to put ...
8
votes
7answers
669 views

Should a surrogate key ever be exposed to a user?

Often in a table that has no natural key, it is still useful for users to be able to have a uniquely generated identifier. If the table has a surrogate primary key (and in such a case you would ...
13
votes
4answers
480 views

Do these specific tables need surrogate keys?

Background I have this tables +-------------------------+ +------------------------+ |Airport | |Country | |-------------------------| |------------------------| ...
1
vote
2answers
315 views

External id for every relational table

Should I add an external id for every table? For example if I have these tables : Customer ======================================= Id Name Gender --------------------------------------- ...
5
votes
2answers
1k views

Handling subscriptions, balances and pricing plan changes

Preamble My aim is to create reusable code for multiple projects (and also publish it on github) to manage subscriptions. I know about stripe and recurring billing providers, but that's not what this ...
0
votes
4answers
225 views

Create a database for database tables

I'm working on a project where I have to give users the ability to build their own tables, each user can create more than one table. (the type of all data is string). The user can create a table and ...
12
votes
6answers
1k views

In plain English, what is a database index most similar to?

Setup: Suppose you are teaching an introduction to Databases class, the students are CS students that have a working knowledge of tree structures, how they can speed up searches, and have probably ...
1
vote
3answers
698 views

Should lookup tables enumerating strings have an integer primary key?

When I learned relational databases, the prof said that one would "almost always" want an artificial int as the primary key in a table, but did not specify what the exceptions are. At some time I ...
3
votes
3answers
791 views

How should I represent an enumerated type in a relational database?

I am working on developing a relational database that tracks transactions that occur on a device I'm working on for my company. There are different types of transactions that could occur on the ...
2
votes
2answers
168 views

Should database-models (conceptual or physical) be reviewed by DBAs?

Where I work, new applications that are being developed that will use their own relational database, must have their database-models (conceptual, then physical ) reviewed and aproved by DBAs. Things ...
2
votes
1answer
341 views

Designing persistence schema for BigTable on AppEngine

I have tried to design the datastore schema for a very small application. That schema would have been very simple, if not trivial, using a relational database with foreign keys, many-to-many ...
7
votes
5answers
1k views

Predicting advantages of database denormalization

I was always taught to strive for the highest Normal Form of database normalization, and we were taught Bernstein's Synthesis algorithm to achieve 3NF. This is all very well and it feels nice to ...
4
votes
3answers
610 views

Which aspect of normal forms do entity-attribute-value tables violate, if any?

I'm not asking if EAV tables are good or bad. I'm wondering if they are considered "normalized", and if not, why? If they aren't normalized, which normal form are they violating and why?
0
votes
1answer
859 views

Design Pattern for Complex Data Modeling

I'm developing a program that has a SQL database as a backing store. As a very broad description, the program itself allows a user to generate records in any number of user-defined tables and make ...
14
votes
1answer
6k views

When should you use a document vs relational vs graph database? [closed]

For the purposes of discussion let's consider a FourSquare scenario. Scenario Entities: Users Places Relationships: Checkins: users <-> places, many to many Friends: users <-> users, ...
2
votes
2answers
1k views

Storing translation data as JSON column

We're deciding on how to store translations of some descriptions of database items. We could go the traditional way and keep a translations table (and a language table and an object_translation ...
1
vote
1answer
2k views

Books that every software system architect/data modeller must read [closed]

I hope I'm not confusing the term system architecture with the data model/database. Is there a book out there that's the standard for describing best practices, design methodologies, and other ...
6
votes
2answers
369 views

What are some useful resources for learning tuple calculus?

I've been trying to go over my tuple calculus over and over again. What's worse is that my professor and tutors are not much help during their hours. Does anyone know a good place to learn about ...
11
votes
10answers
904 views

How could RDBMSes be considered a fad?

Completing my Computing A-level in 2003 and getting a degree in Computing in 2007, and learning my trade in a company with a lot of SQL usage, I was brought up on the idea of Relational Databases ...