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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I mainly depends on how those other 9 lastnames have to be used later.
please clarify
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.