Geek asks and answers

Table Design for SQL

I hope you can help me here - I had a question about developing a SQL table. I know how to write to a database and execute queries using C #, but I never had to develop it. So I thought I would give it a shot, just our interest.

Suppose I have a table called family_surname. Inside this table there may be x the number of family members, say, from 2 people to 22 people. How can I refer to family_members vs family_surname?

So, I would FAMILY SURFACE Smith, Jones, Brown, Taylor, et al.

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

I really do not want the "last name" to be indicated 8 times for each member - ideally, the row of the last name will refer (or somehow point) to the corresponding row in another table. And here they have problems!

I hope I have a point; as I say, they are just curious, but I would like to know how to do this with two tables.

Anyway, thanks for your help, I appreciate that.

EDIT Thank you who commented - of course, the helpful information here, which I appreciate. I read and research some bits of SQL and peices, and so far it is pretty good. Thanks again guys!

+5
source share
4 answers

What you are asking is the question of normalization. The table will look like this:

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

, I. , , , , surnameid , . .

, , - . . , "" "", . , .

+3

, , , , ....

( ), , . , "jones" . / . , (), , .

, , , PersonId, FirstName, LastName. , , . , , , .., "".

. , ..

+2

:

  • (INT ID, SURNAME VARCHAR2 (200))
  • CREATE TABLE DETAILS (INT ID, (SURNAME_ID) (ID), PARAM1, PARAM2.....)

(, 1, 2,....). : 1. () 2. (PARAM1, param2...)

0

, "" 8 .

? , ?

, , ( , ), , , .

, , :

  • , JOIN, - .
  • ( ) // .
    • , , " " .
    • (, ) (. ) .
    • Can a surname exist without any person? If not, there is no good declarative integrity to ensure this. In the best case, you will need to write triggers.
  • In the end, you can not save a lot of space - an additional table will have its own overhead storage costs (for example, the index "under" the primary key), which can "eat" most of the expected storage.
0
source

More articles:


All Articles