Lets say you want to create a listing of widgets The Widget Manufacturers all create widgets with different number and types of attributes. And the Widget sellers all have different preferences on what type and number of attributes they want to store in the database and display. The problem here now is that each time you add in a new w...
In my experience, this has been a contentious issue between "backend" (database developer) and "frontend" guys (application developer, client and server side). There have been many heated pub discussions on this subject. I just want to know is it just people have different mindsets, or lazy to learn more and feel comfortable in what ...
A DB design question: when do you decide to use 1 to 1 relation tables? One of the places I see this is, for example, when you have a User and UserProfile table, people split them instead of putting all columns just in a User table. Technically, you can just put all the columns in one table since their relationship is 1-to-1. I know...
How would you represent a temporal many-to-many relation in SQL? Under non-temporal circumstances one would use a junction table (aka link/bridge/map) to connect the two sides. Is adding temporal tracking as simple as including a ValidStart and ValidEnd columns on the junction table? If you have done this, what issues (if any) did you r...
I was thinking the other day on normalization, and it occurred to me, I cannot think of a time where there should be a 1:1 relationship in a database. Name:SSN? I'd have them in the same table PersonID:AddressID? Again, same table. I can come up with a zillion examples of 1:many or many:many (with appropriate intermediate tables), bu...
I am not very familiar with databases and the theories behind how they work. Is it any slower from a performance standpoint (inserting/updating/querying) to use Strings for Primary Keys than integers? ...
Given the following tables in ActiveRecord: authors sites articles I don't know how to express that an author is paid a different amount depending on the publication, but that authors working for the same publication have different rates: John publishes an article in Foo for $300 John publishes an article in Bar for $350 John publi...
I have been wondering what the best practices or ramifications are in setting up the PK in a M2M table in SQL Server. For instance: I have 2 tables Users Roles I am making a new table UserRole Which has 2 fields RoleId & UserID now should I create a UserRoleID as the PK and make UserID and RoleID the FKs make the PK UserID ...
I'm trying to create a migration for a simple table that is just used as an enum. So I want to populate the table immediately with its values. I tried the following: class CreateUserTypes < ActiveRecord::Migration def self.up create_table :user_types do |t| t.column :type, :string t.timestamps end end def self....
When you run: rake db:migrate the only files that are being processed are those in db/migrate/ right? Well since relationships such as one-to-one, one-to-many and many-to-many are defined in the in app/models/ , how does Rails enforce such relationships? After I do my migration and look at the generated database schema, I can't see a...
Quick question, I was curious if there is any difference between a database table that has been defined in one shot, and one that has had columns added to it over time. Does the one with added columns performance suffer in someway? Anyways database vendor isn't too relevant here unless there are vendor differences for this case. Thanks...
Hi, I am currently in the process of looking at a restructure our contact management database and I wanted to hear peoples opinions on solving the problem of a number of contact types having shared attributes. Basically we have 6 contact types which include Person, Company and Position @ Company. In the current structure all of these ...
Which is your preference? Let's say we have a generic Product table that has an ID, a name, and a foreign key reference to a category. Would you prefer to name your table like: CREATE TABLE Products ( ProductID int NOT NULL IDENTITY(1,1) PRIMARY KEY, CategoryID int NOT NULL FOREIGN KEY REFERENCES Categories(CategoryID), Pr...
I have a mysql database with a table of ebay users, and their individual auctions. What I wish to do is to be able to "monitor" certain users, which would basically include retriving all of the auctions of any monitored users. I would like to know the best way to do this. I am thinking of having another table, with just one column cont...
As a rule is it better to use native primary keys (ie existing columns or combination of columns) or set your primary key to an auto generating row of integers? EDIT: It has been pointed out to me that this very similar to this question. The consensus here is to use surrogate keys, which was my natural inclination, but my boss told me ...
I have to maintain an application that has a lot of columns that are of the text data type, with multiple values inserted into them delimited with commas, slashes or sometimes even the pipe (|) character. I'm trying to figure out why on Earth you would ever want to do this. For an example, an orders table has a column called details th...
IGNORE_DUP_KEY = ON basically tells SQL Server to insert non-duplicate rows, but silently ignore any duplicates; the default behavior is to raise an error and abort the entire transaction when there are duplicates in a column that doesn't allow them. I've worked with a ton of data that normally has at least one duplicate when there shou...
What are your thoughts on this? I'm working on integrating some new data that's in a tab-delimited text format, and all of the decimal columns are kept as single integers; in order to determine the decimal amount you need to multiply the number by .01. It does this for things like percentages, weight and pricing information. For examp...
I'm writing a survey website where users are asked multiple choice questions. Designing the database schema is pretty trivial in the case of a single language, but as soon as there can be multiple, the schema becomes fuzzier. My gut is telling me that I'd have the following tables (with all the nasty bits removed): questions (id) ...
Somewhat related to my question about integers instead of decimals; my vendor provides a lot of "boolean" fields in the char(1) format (i.e. Y/N). Some of these fields legitimately cannot be a boolean because they can have several values, but the majority can be treated as a boolean. In my previous question the advice was to do what's ...