According to the definition, a Junction Table (bridge table/link table) is used for many-to-many relationships, when used like this: CREATE TABLE Users ( UserLogin varchar(50) PRIMARY KEY, UserPassword varchar(50) NOT NULL, UserName varchar(50) NOT NULL ) CREATE TABLE Permissions ( PermissionKey varchar(50) PRIMARY KEY, PermissionDesc...
I was watching a screencast where the author said it is not good to have a primary key on a join table but didn't explain why. The join table in the example had two columns defined in a Rails migration and the author added an index to each of the columns but no primary key. Why is it not good to have a primary key in this example? c...
create_table :categories_posts, :id => false do |t| t.column :category_id, :integer, :null => false t.column :post_id, :integer, :null => false end I have a join table (as above) with columns that refer to a corresponding categories table and a posts table. I wanted to enforce a unique constraint on the composite key category_id, p...
I am looking to optimize my LINQ query because although it works right, the SQL it generates is convoluted and inefficient... Basically, I am looking to select customers (as CustomerDisplay objects) who ordered the required product (reqdProdId), and are registered with a credit card number (stored as a row in RegisteredCustomer table wi...
I have a table Messages with columns ID (primary key, autoincrement) and Content (text). I have a table Users with columns username (primary key, text) and Hash. A message is sent by one Sender (user) to many recipients (user) and a recipient (user) can have many messages. I created a table Messages_Recipients with two columns: MessageID...
Hi guys, Not sure why I can't figure this one out. Basically, I have two tables with a many-to-many relationship so I have a junction table inbetween them. For an example, consider the following database schema: Restaurant (id, restaurant_name, suburb) RestaurantCuisine (restaurant_id, cuisine_id) Cuisine (id, cuisine_name) So, ma...
So, yesterday I asked 2 questions that pivoted around the same idea: Reorganizing a database that A- wasn't normalized and B- was a mess by virtue of my ignorance. I spent the better part of the day organizing my thoughts, reading up and working through some tests. Today I think I have a much better idea of how my DB should look and act,...
Let's say we have a Product table, and Order table and a (junction table) ProductOrder. ProductOrder will have an ProductID and an OrderID. In most of our systems these tables also have an autonumber column called ID. What is the best practice for placing the primary key (and therefor clustered key)? Should I keep the primary key o...
With this kind of design, I would like to create a functionality with which to add and delete records from the junction table. The case is from when editing an employee and selecting what exams they can take. In the left list box would be the exams that they aren't eligible for (yet, anyway) and the exams that they are eligible for on ...
In my database application, when I try to attach a page to a site, I get the following error: System.InvalidOperationException: Can't perform Create, Update or Delete operations on 'Table(Junc_Page_Site)' because it has no primary key. I intend a page to be able to be added to multiple sites - by using a junction table. Below is the ...
I have Object1 and junction table and Object2. Object2 is table that has many junction tables, but can have only one junction table torefrencing to it. When table Object1 is removed, then junction table and Object2 should be removed. How can I make foreign keys in this situation? But when Object2 is removed, then only junction table shou...