Assume a table that uses a natural key and has a number of existing rows. What would be the easiest way to retrofit a surrogate key column and populate it with unique values using MySql? I.e. transform table employees ( social_security_no varchar(20), ... constraint emp_pk primary key (social_security_no) ); to table emp...
Entity Framework magically interprets the following table structure as a many-to-many relationship. table foo (int id) table foo_bar (int foo_id, int bar_id) table bar (int id) But if the join table has any additional fields it will instead be interpreted as two one-to-many relationships. I am using a database in which the join table...
I am maintaining a data warehouse with multiple sources of data about a class of entities that have to be merged. Each source has a natural key, and what is supposed to happen is that one and only one surrogate key is created for each natural key for all time. If one record from one source system with a particular natural key represent...
I am using PHP, AS3 and mysql. I have a website. A flash(as3) website. The flash website store the members' information in mysql database through php. In "members" table, i have "id" as the primary key and "username" as a unique field. Now my situation is: When flash want to display a member's profile. My questions: Should Flash pas...
I'm mapping an existing database structure into Django models. I have a many-to-many structure where the association table is natural-keyed: CREATE TABLE foo (id INTEGER PRIMARY KEY); CREATE TABLE bar (id INTEGER PRIMARY KEY); CREATE TABLE foo2bar (foo_id INTEGER REFERENCES foo(id), bar_id INTEGER REFERENCES bar(id...
I realise there might be similar questions but I couldn't find one that was close enough for guidance. Given this spec, Site --------------------------- SiteID int identity Name varchar(50) Series --------------------- SiteID int SeriesCode varchar(6) ... --SeriesCode will be unique for every unique SiteID Episod...
Hi Guys, Sorry in advance as this question is similar (but not the same!) to others. Anyway, I need to be able to generate surrogate keys in more than one location to be synchronized at a later time. I was considering using GUIDs, however these keys may have to appear in the parameters of a URL and GUIDs would be really complicated and...
Can anyone of you please tell me which is the best data type for a surrogate key (number or varchar2)? I have read that surrogate key works best with the number type. But is the performance seriously have major impact with the data types. And is it database dependent too? reply would be much appreciated! ...
I have been reading the heated debates on composite vs surrogate keys on Stack Overflow and other websites, and even though puzzled about some of the arguments given, I feel I am aware of the pros and cons of each. I feel tempted to go for surrogate keys, but now I have another question. Let me explain my situation. I have a table consi...
I have to modify an existing table in a Oracle 10g DB with a few thousand records to add a surrogate autonumber key. One way that comes to my mind is to Create a new sequence Create the id column, allowing null values Updating the id column with the sequence Alter table to add "not null" and "primary key" for the new id column Is th...
I am writing a new app with Rails so I have an id column on every table. What is the best practice for enforcing domain constraints using foreign keys? I'll outline my thoughts and frustration. Here's what I would imagine as "The Rails Way". It's what I started with. Companies: id: integer, serial company_code: char, unique, no...
I got to populate FactTable with 12 lookups to dimension table to get SK's, of which 6 are to different Dim Tables and rest 6 are lookup to same DimTable (type II) doing lookup to same natural key. Ex: PrimeObjectID => lookup to DimObject.ObjectID => get ObjectSK and got other columns which does same OtherObjectID1 => lookup to DimOb...
First off, we are using JPA 2.0 and Hibernate 3.5 as persistence provider on a PostgreSQL database. We successfully use the sequence of the database via the JPA 2.0 annotations as an auto-generated value for single-field-surrogate-keys and all works fine. Now we are implementing a bi-temporal database-scheme that requires a mixed key i...
Object A has a one-to-many association: many object B. When I look in the database -- TableB -- I'd like to see the unique, readable string A.Name instead of having always to join or subselect on a surrogate integer identifier to see the name. I can map Name as the identifier for A, but this causes lots of extra SELECT queries because ...
Which one is the best practice and Why? a) Type Table, Surrogate/Artificial Key Foreign key is from user.type to type.id: b) Type Table, Natural Key Foreign key is from user.type to type.typeName: ...
My table schema is something like this 1. Master table : Clause Columns ClauseID = surrogate pk (identity) ClauseCode = nvarchar user specified value Class = nvarchar FK to a master class table etc... ClauseCode + Class = candidate key for this table 2. Master table : GroupClause Columns GroupClauseID = surrogate pk (identi...