Table design for SQL

Question

i hope you can help me out here - i have a question about designing a SQL table. I know how to write to a database and perform queries using C#, but i have never really had to design one. So i thought i would give it a shot just our of interest.

Lets assume i have a table named family_surname. Within that table, there could be x amount of family_members, say ranging from 2 people to 22 people. How can i reference family_members against family_surname?

So i would have FAMILY SURNAME Smith, Jones, Brown, Taylor, etc.

And then Smith may have 5 members, to where i would like to record age, height, weight, whatever. Jones may have 8 members - it could vary between families.

I dont really want 'surname' listed 8 times for each member - ideally the surname row would reference (or somehow point to) a corresponding row in another table. And thats where im having trouble!

I hope i make sense; like i say, im just interested, but i would like to know how to do this with two tables.

Anyway, thank you for your help, i appreciate it.

EDIT Thank you to everone who commented - certainly some useful information here, which i appreciate. Im reading up and researching some SQL bits and peices, and so far its pretty good. Thanks again, guys!

Answer 1

What you are asking is a question about normalization. The table would look like:

Create table surname (
    SurnameID int,
    Surname varchar(255)
)

The other tables would reference the surname by using the I'd. Also, you probably want surnameid to be unique, a primary key, and auto incrementing. Those are more advanced topics.

That said, I'm not sure surname is a great candidate for splitting out like this. One reason to normalize data is to maintain relational integrity. In this case, it means that when you change "Smith" to "Jones", all the Smiths change at once. I don't think this is a concern in your case.