How to create application with single table with primary key

Rails
1

I’m trying without any succes to create application in which I have
one keyed table. The table structure is:

CREATE TABLE employees (
pin INTEGER PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL
);

Whenever I enter new record, I need the the filed pin is also filled
by user. Whenever a user edit the table, there should be option to
edit or not edit the pin field.

After creating a database in SQL server. Editing the model:
class Employee < ActiveRecord::Base
has_many :shots
set_primary_key ‘pin’
end

and creating the scaffold
script/generate scaffold Employee

It simply does not work. When I try to access the
http://localhost:3000/employees/new page, I got error

undefined method `pin_before_type_cast’ for #Employee:0x4092d5d0

Extracted source (around line #5):

2:
3:
4:

Pin

5: <%= text_field ‘employee’, ‘pin’ %>


6:
7:

First name

8: <%= text_field ‘employee’, ‘first_name’ %>

Can anyone point me to simple example how to create controller and
view for editing simple keyed table?

–
Radek Hnilica

2

Extracted source (around line #5):

2:
3:
4:

Pin

5: <%= text_field ‘employee’, ‘pin’ %>

They should be able to edit their primary key as they could ruin the DB
by entering a number someone else already has. I would say you have to
delete lines 4-5.

3

joey__ wrote:

They should be able to edit their primary key as they could ruin the DB
by entering a number someone else already has. I would say you have to
delete lines 4-5.

But Pin is a value the MUST enter. And they know the correct value.
Users also cant ruin db because Pin is PRIMARY KEY.

4

Radek wrote:

joey__ wrote:

They should be able to edit their primary key as they could ruin the DB
by entering a number someone else already has. I would say you have to
delete lines 4-5.

But Pin is a value the MUST enter. And they know the correct value.
Users also cant ruin db because Pin is PRIMARY KEY.
If the primary key is going to get refferenced, then they could mess it
up. It would be better to have an id as the primary field.

5

You need to have an id column (an actual column named “id” all
lowercase, an
int) as the primary key or you have to tell rails to use the column you
create.

On 12/27/05, Radek [email protected] wrote:

Whenever I enter new record, I need the the filed pin is also filled
script/generate scaffold Employee
4:

Pin

Radek Hnilica

–
Posted via http://www.ruby-forum.com/.

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

–
Steven R.
web application & interface developer
http://www.zerium.com
[phone] 404-488-4364

6

Steven R. wrote:

You need to have an id column (an actual column named “id” all
lowercase, an
int) as the primary key or you have to tell rails to use the column you
create.

Yes, I have primary key column pin, and sayz the rails it’s primary key
set_primary_key ‘pin’

But I need to enter a value to that field by myself (user) vhile
creating a new record.

But the error rises
undefined method `pin_before_type_cast’ for #Employee:0x4092d5d0

Extracted source (around line #5):

2:
3:
4:

Pin

5: <%= text_field ‘employee’, ‘pin’ %>


6:
7:

First name

8: <%= text_field ‘employee’, ‘first_name’ %>

The question is: how a have present and edit/enter data into table whose
NATURAL key is given by user and maybe or not integer value.

–
Radek Hnilica

7

Henning K. Pedersen wrote:

I gather that the best tip here would be to have an ID column
nonetheless, making that your table’s primary key, and letting Rails
handle it’s magic around that column (base relationships on it [foreign
key] and so forth). But still use a “PIN” column like you’re describing,
but instead of making it a primary key, make it a Unique Index. It’s

Do you suggets something like:

CREATE TABLE employees (
id SERIAL PRIMARY KEY, – nasty hack to comply with Rails
pin INTEGER UNIQUE, – the natural primary key
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL
);

For the first, it seems to work but I only tested this table. Should be
fully tested if this added complexity doesn’t break things to much.
Because all other constraints and references in schema are done to
NATURAL PRIMARY KEY which is not PRIMARY KEY now.

generally bad form to make a primary key column something that can be
edited by the user, since editing it would break all forms of
relationships and induce unneeded complexity into your application.

??? I do not underestand. pin is pin and it’s a natural primary key. It
shuld be entered in all it’s size. It’s natural and not complex.
Complexity is hack arround.

– Radek

8

Radek skrev:

Yes, I have primary key column pin, and sayz the rails it’s primary key
set_primary_key ‘pin’

But I need to enter a value to that field by myself (user) vhile
creating a new record.

[snip]

The question is: how a have present and edit/enter data into table whose
NATURAL key is given by user and maybe or not integer value.

I gather that the best tip here would be to have an ID column
nonetheless, making that your table’s primary key, and letting Rails
handle it’s magic around that column (base relationships on it [foreign
key] and so forth). But still use a “PIN” column like you’re describing,
but instead of making it a primary key, make it a Unique Index. It’s
generally bad form to make a primary key column something that can be
edited by the user, since editing it would break all forms of
relationships and induce unneeded complexity into your application.

Regards,
Henning P.

9

Matthew P. wrote:

On Wed, Dec 28, 2005 at 06:43:32AM +0100, Radek wrote:

Henning K. Pedersen wrote:

generally bad form to make a primary key column something that can be
edited by the user, since editing it would break all forms of
relationships and induce unneeded complexity into your application.

??? I do not underestand. pin is pin and it’s a natural primary key. It
shuld be entered in all it’s size. It’s natural and not complex.

A PIN (in the ATM card sense of the word, at least) is a really, really
bad
choice of primary key. It lacks:

maybe I have had describe pin more. It’s not a PIN in ATM sense. It’s
Personal Identification Number
It’s a unique number which can’t be assigned twice. And I also have no
control how it’s this pin assigned. I should only write it into
database.

Complexity is hack arround.
A unique and automatic primary key isn’t complexity, it’s The Right Way.
As
an aside, here’s a nice example of poor choice of primary key, and it’s
consequences:

Uniquely Addressing - The Daily WTF

Unless you want to end up mentioned on that site one day, I’d rethink
your
choice of primary key.

I carefully construct a db structure, I very carefully select the
primary keys. My problem is how this structure present to rails. How
present tables whose primary key isn’t numeric and should be entered in
new records and sholud be editable under some circumstances.

– Radek

10

On Dec 28, 2005, at 12:22 AM, Radek Hnilica wrote:

shuld be entered in all it’s size. It’s natural and not complex.
have no
control how it’s this pin assigned. I should only write it into
database.

Hello Radek.

Your question has been asked and answered a number of times.

Spend some time learning Rails. Understand one thing up front: Rails
is OPINIONATED. It doesn’t work the way that YOU want it to, it works
the way that David wants it to.

Many people (and growing exponentially it seems) agree with the way
that David wants it to work. Some will never agree with him, which
isn’t a good or a bad thing, it’s just a fact.

David wants your DB structure to follow certain rules. Synthetic
primary keys are one of the rules. If you force Rails (which is likely
possible) to conform to your way of thinking, you’re going to find
that you don’t like Rails much because you don’t understand it very
well.

When I first started, I immediately decided that I didn’t like David’s
pluralization scheme. After all, I had designed many databases and had
heartfelt beliefs about how they should be designed. Pluralization was
not one of those beliefs. In my case, I agreed wholeheartedly from past
experience that natural keys are horrible primary keys, so I had a leg
up on you on that one.

I designed the DB my way, and forced Rails to use the ID columns of
my creation, and table names of my creation. But after a week, I
realized I was struggling with details that nobody else was dealing
with, and my code didn’t read as fluently as the Rails code that
everyone else was posting to this list. The code that followed David’s
rules read like English, my code read like traditional computer code,
very unnatural…so I just threw in the towel and started over,
following the rules.

In no time I was convinced it was the right decision.

In my opinion, you need to:

  1. Throw in the towel on your DB design and go with the flow. Give it
    a few days, and see if you like it. If you don’t like it, use
    another framework.

  2. Give up now and use another framework.

What you said about adding complexity to suit Rails is true to an
extent. You have a natural primary key and you “shouldn’t” need
another synthetic one. However, if you manage to force Rails to
work with your schema, you will be adding far more complexity in
your code to handle the special case than you would add to the DB
schema by following David’s and Rails’ assumptions.

–
– Tom M.

11

On Wed, Dec 28, 2005 at 06:43:32AM +0100, Radek wrote:

Henning K. Pedersen wrote:

generally bad form to make a primary key column something that can be
edited by the user, since editing it would break all forms of
relationships and induce unneeded complexity into your application.

??? I do not underestand. pin is pin and it’s a natural primary key. It
shuld be entered in all it’s size. It’s natural and not complex.

A PIN (in the ATM card sense of the word, at least) is a really, really
bad
choice of primary key. It lacks:

  • Uniqueness: if two customers choose the same PIN, you’re stuffed –
    something that is quite likely to happen as you get more customers

  • Immutability: customers can change their PIN (in fact, they should
    do it
    regularly, for security), and every time they change their PIN you
    have to do a pile of queries to ensure that you update your foreign
    key references (or use ON UPDATE CASCADE, I suppose)

I’ve seen the result of a system which didn’t allow two people to have
the
same PIN – it didn’t take too long before new inductees had to enter a
PIN
two or three times before they got one that was available.

Complexity is hack arround.

A unique and automatic primary key isn’t complexity, it’s The Right Way.
As
an aside, here’s a nice example of poor choice of primary key, and it’s
consequences:

Unless you want to end up mentioned on that site one day, I’d rethink
your
choice of primary key.

  • Matt
12

On Wed, Dec 28, 2005 at 11:35:11AM -0800, Tom M. wrote:

Hello Radek.

Your question has been asked and answered a number of times.

Not exactly. Many times was answered a question I do not ask. :slight_smile:

Spend some time learning Rails. Understand one thing up front: Rails
is OPINIONATED. It doesn’t work the way that YOU want it to, it works
the way that David wants it to.

Yes I’m trying to learn.

Many people (and growing exponentially it seems) agree with the way
that David wants it to work. Some will never agree with him, which
isn’t a good or a bad thing, it’s just a fact.

I do not want talk things, I can’t change. It’s waste of time.

David wants your DB structure to follow certain rules. Synthetic
primary keys are one of the rules. If you force Rails (which is likely
possible) to conform to your way of thinking, you’re going to find
that you don’t like Rails much because you don’t understand it very
well.

I underestand synthetic keys where’s none. But forcing it everywhere
makes me completly lost. I’m desoriented, its just only pain. Yes,
I’ll try to to change my mind, and change my tools.

When I first started, I immediately decided that I didn’t like David’s
pluralization scheme. After all, I had designed many databases and had
heartfelt beliefs about how they should be designed. Pluralization was
not one of those beliefs. In my case, I agreed wholeheartedly from past
experience that natural keys are horrible primary keys, so I had a leg
up on you on that one.

Pluralization is another things which hit me first on the begining. I
do not want talk about it. I just write one sentence I think the last
time I was thinking about pluralization. “You should know English,
unfortunately some unknown dialect of English”.

I designed the DB my way, and forced Rails to use the ID columns of
my creation, and table names of my creation. But after a week, I
realized I was struggling with details that nobody else was dealing
with, and my code didn’t read as fluently as the Rails code that
everyone else was posting to this list. The code that followed David’s
rules read like English, my code read like traditional computer code,
very unnatural…so I just threw in the towel and started over,
following the rules.

Only one word, I know better computer code than English. If the
programming langue will be English, I’ll be out of work.

In no time I was convinced it was the right decision.

In my opinion, you need to:

  1. Throw in the towel on your DB design and go with the flow. Give it
    a few days, and see if you like it. If you don’t like it, use
    another framework.

  2. Give up now and use another framework.

I promis I’ll do it. I’ll do it both ways. Simultaneously.

What you said about adding complexity to suit Rails is true to an
extent. You have a natural primary key and you “shouldn’t” need
another synthetic one. However, if you manage to force Rails to
work with your schema, you will be adding far more complexity in
your code to handle the special case than you would add to the DB
schema by following David’s and Rails’ assumptions.

I need to know where the border of the complexity in Rails is. What
and what not leads to it. I’m not looking or one size fits all
system. I just need to know for what size which system fits.

Question: How do you handle the natural keys. It does not matter, if
there are some synthetic keys or not, It doesn matter if the
application works or not if there are duplicity in natural keys.

Question: How do you handle constraints. Especially if some other
application will access the data.

P.S. It’s more important to know what you can’t do then what you can.
Because if you know what you can’t do it’s waste of time try to do it.

P.S. Thanks for explanation.

–
Radek Hnilica http://www.hnilica.cz

No matter how far down the wrong road you’ve gone, turn back.
Turkish proverb
… so turn back … Now!

13

On Dec 28, 2005, at 2:10 PM, Radek Hnilica wrote:

there are some synthetic keys or not, It doesn matter if the
application works or not if there are duplicity in natural keys.

Question: How do you handle constraints. Especially if some other
application will access the data.

I handle natural keys as I have always handled alternate keys in DB
design. I create unique constraints on them. Nobody ever said that
it’s a bad idea to have more than 1 key on a table.

Other applications can access data this way (the same way Rails apps
would!):

select *
from people_relations
where person_id = (select id
from people
where pin = “whatever”)

For instance. Obviously there’s no need for subselect, but I think
it makes the point clearly enough. If joins are more your style,
look at it this way:

select *
from people,
people_relations
where people.pin = “whatever”
and people.id = people_relations.person_id

Believe me, it took some time to get used to foreign keys that did
NOT contain the name of the original table (due to pluralization)
but it DOES make a lot of sense, even at the SQL level.

I’m not clear why this is so disorienting, but I do understand that
learning new stuff sometimes feels like getting cheese cloth pulled
through your brain.

Think of the primary (synthetic!) id columns for each table as there
for the singular purpose of forming relations between tables. This
entirely and completely removes the dreaded possibility of primary
key mutability.

P.S. Rails is not attempting to be an English programming language.
If I indicated that, I didn’t particularly mean to. The point
is that the code is very readable and unambiguous, and the
pluralization aids greatly in determining whether the object
your getting is a single object or collection of objects. Rails
conventions will make code maintenance in the future fantastically
simpler than other systems, IMHO.