I'm trying to think ahead a little and avoid myself some extra pain if possible. I've had this problem in past applications and have usually opted for the most verbose approach but would like the opinions of a few others. If you have a basic table such as below, is it wise, and/or more efficient to include a field which includes a calc...
I'm trying to figure out the "best practices" for deciding whether or not to add an auto-incrementing integer as the primary key to a table. Let's say I have a table containing data about the chemical elements. The atomic number of each element is unique and will never change. So rather than using an auto-incrementing integer for each ...
This question suddenly popped into my head... I have a table that ties two other tables together based on their ID. The CREATE TABLE looks like this: CREATE TABLE `ticket_contact` ( `ticket_id` INT NOT NULL, `entity_id` INT NOT NULL, `notify` INT NOT NULL DEFAULT 0, PRIMARY KEY (`ticket_id`, `entity_id`...
I'd like some advice designing my database tables for a small project I'm working on. Assuming in this systems I have articles, subarticles, and comments. Each article can have subarticles. Both articles and subarticles can have comments. I considered having an autoincrementing int primary key for each table (i.e. articleId, subarticl...
This is a general database question, not related to any particular database or programming language. I've done some database work before, but it's generally just been whatever works. This time I want to plan for the future. I have one table that stores a list of spare parts. Name, Part Number, Location etc. I also need to store which d...
We are storing the metadata of the content in the database tables. Out which there is a column named keywords which contains the keywords related to the content. So, should we normalise the table based on the keyword column because the keyword column will be holding multiple values. ...
Let's say you're designing the DB schema for the next stack overflow and more specifically the part of the schema that handles question ratings. I assume you'd use a table like: ratings(question_id, user_id, rating) ... that will both record ratings and make sure no user votes twice on the same question. That table alone could handle r...
I am quite ashamed to ask this, but recently there has been a situation where I need to create a single table for three different types of banking entities that are related to each other. Let me explain. Imagine a BANK table that holds details of either a Governing Bank, or a regular Bank that operates rural branches, or the rural branc...
I'm about to switch some of my entities from identity to hilo id-generator. I'm don't have a clue how the tables holding the next-high values should be designed. should I use a single table for all entities, or for a group of related entities? should I use another table for each entity? should I use a single table with another row or ...
Hi there, I have several models whose records AND associations can have two states that must be persisted. Final, and Draft. Here's a little more detail: If the model is "application form" and the user submits a final application form, then I need to store and be able to retrieve that final application form. If, at a later date, the u...
Hi, I have the following database model: [User] Id Name [Role] Id Name [UserRole] UserId RoleId IsActive And I want to create a nice way to represent this relationship and the property that is in it with objects without creating a class to represent UserRole table. Any ideas? Thanks a lot! ...
In the process industry, lots of data is read, often at a high frequency, from several different data sources, such as NIR instruments as well as common instruments for pH, temperature, and pressure measurements. This data is often stored in a process historian, usually for a long time. Due to this, process historians have different req...
There is a term that identifies a table column that refers to a column in another table. That is the term "foreign key". Is there a term that identifies a table column that refers to a column in the same table? An example of such column is the "item_parent" column which refers to the "item_id" column in the following table: items(item_...
As stated in the title, when designing databases, what is the preferred way to handle tables that have multiple columns that are just storing true / false values as just a single either or value (e.g. "Y/N: or "0/1")? Likewise, are there some issues that might arise between different databases (e.g. Oracle and SQL Server) that might affe...
I have a web app that I built for a trucking company that I would like to offer as SaaS. What is the best way to design the database? Should I create a new database for each company? Or should I use one database with tables that have a prefix of the company name? Or should I Use one database with one of each table and just add a compan...
I currently build a CMS system that need to save a lot of pictures per article. I have a lot of questions :-) I need to show the pictures in a few sizes, with or without watermark. In addition I need to have the original picture too, for archive and admin purpose. What that I think to do right now is to save the pictures in the database...
I will be using the PhidgetSBC as the device that will be connected to the various sensors (Temperature, humidity, CO2, on/off sensors, range finders, etc). It will also be used to control certain things (think home brew home automation). The code on the SBC will be handling data capture of the sensor readings (if polling it would be e...
Two of my colleagues and I are building a system to do all sorts of hydrology and related stuff. It has a lot of requirements and have a good number of tables. We are handling all sorts of sampling that it is done within this scope (hydrology) and we are trying to figure out a way to do it in a less painful way. Sometimes we need to ge...
The lead developer on a project I'm involved in says it's bad practice to rely on cascades to delete related rows. I don't see how this is bad, but I would like to know your thoughts on if/why it is. ...
Hi folks, I'm going to do an indexed view, based on three tables with inner and outer joins between them (SQL Server 2005). I will run all kind of queries against this view. So, I wonder what is the best way to choose which index to be clustered. What are the criteria or is there any tools to help me around. (Sorry if my question is d...