SQL Server - Table Attributes

Hi,

I basically have a table called name...
it has

first name
last name
etc...

it now needs to support for eg. 10 more last names...

so without...
firstname
lastname
lastname2
lastname3
lastname4
.
.etc
.

is there a better way of doing this? like table attributes or something?

im using LinqToSql and .NET on the front end.
LVL 1
s34nAsked:
Who is Participating?
 
Máté FarkasDatabase Developer and AdministratorCommented:
Your possibilities:
1. Storing values in one Field (for example LastNames) separated by something (slash, semicolon,  etc.)
2. Storing values in on Field which is xml type
3. Creating a new table (called LastNames) with a person id and a LastName column and store each lastname in the rows and the personid will connect them to the original record
All of these solutions are dynamic which means that you can extend the number of lastnames of a person without changing the sql database schema.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I mainly depends on how those other 9 lastnames have to be used later.
please clarify
0
 
Shannon_LowderCommented:
The idea #3 from agux3e appears to be the best option, since you could have firstname, lastname in that table, then JOIN back to the original table on first name to get your list of last names.  The only problem with that would be, what if you had two "John" in your table?

In your primary table if you had a personID as the primary key, then in your lastNames table, you could join on that same column, but it would serve as a foreign key in the lastNames table.

---
Shannon Lowder
Database Engineer
toyboxcreations.net
0
 
s34nAuthor Commented:
thanks for your replies!

in my previous area of work, we had database developers, but currently it seems like it doesn't deserve its own role so the software developers end up pulling something together in sql server.

agux3e:
1. I thought of this, but it could get a bit messy; although it would work.
2. This would work quite well, I have some code which already does this...
3. Ill give this one a try, and see how it goes...

Shannon_Lowder:
wouldn't you join back on a foreign key id?

tblName
*P NameID
firstName
middleName

tblSurnames
*P ID
*F NameID
Surname
0
 
Shannon_LowderCommented:
I would.  That's what I was referring to with the personID.  You've called it NameID.  That way it wouldn't matter how many "john" appear in your table, you'd be sure to refer to the correct one by NameID.

By the way, what you're building here is referred to as a tall table.  It's very useful when designing a on-to-many relationship.  But it gets even more interesting when you build an attribute table (primaryKey, ForeignKey, attributeName, attributeValue).  It lets you have many attributes for an entity, and store them all in one list.  It's very useful when you have an unknown number of attributes for an entity.


---
Shannon Lowder
Database Engineer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.