A table with only one column

Question

A table with only one column

huaxing wu

SSC Enthusiast

Points: 125
More actions
September 20, 2005 at 1:44 pm

#165992

I am designing a DB and am struggling which is the best way to define the lookup tables.
The scenaio is that I have a table Documentation, and each documentation has an author, for the author, we only interested in his name in the format 'firstname.lastname'. So there is two way to design these
A) table Documentation           table Author
      DocumentationID               UserName (VARCHAR)
      Title
       AuthorName (VARCHAR)
There is a foreign key between AuthorName in Documentation and
Username in table Author
b) table Documentation           table Author
      DocumentationID               UserID(int)
      Title                                UserName(varchar)
       AuthorID (int)
Foreign Key between two IDs.
I prefer Solution A because it save a look up whenever we need to see the user name, fewer join to get the information. And we can easily make sure that UserName is unique.
But I see people usually use solution B, maybe id is perfered when foriegn key/primary key involved.
Any insight will be greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

If you have 2 tables, it's not too bad, but as the db grows, you'll see that it's gonna be a pain in the ass to update the same information in X tables and keeping everything unique in each table AND globally... You really should use the foreign key approach here.

huaxing wu SSC Enthusiast Points: 125 More actions · Answer 2

Maybe I didn't make it clear. but both solutions use foreign keys. the difference is the first solustion's foreign key will be varchar, while the send one wil be int

webtekkie SSCommitted Points: 1901 More actions · Answer 3

You need to use your second solution. The AuthorID column in Documentation will be a foreign key, and the UserID column in Author table will be the primary key and will be an Identity column so the UserID is automatically assigned.

Your first solution holds no benefit as the Documentation table will hold the authors name (presumably this is what you imagined, as it is a varchar) so there would be no point in having the Author table.

Hope that helps,

Martin

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 4

Solution "A" could also pose problems with duplicate authors based on how the interface is designed, ANSI padding, etc...

"Smith "

"Smith"

" Smith"

Solution "B" is best.

In addition, consider changing the "UserID" field to "AuthorID" in the "Autor" table. It's easier to see the relationships.