Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to create a database listing characters in each book of the A Song of Ice and Fire series, listing the name of the character and the page in which they appear. I could just use three fields (not including the primary key,) Name, PageIntroduced, and BookIntroduced, but the Name field isn't atomic; I could be subdivide it quite a bit. Some characters have titles (Maester, Lord, Ser,) some characters have house names (Stark, Lannister, Baratheon,) plenty have nicknames (Bull, Ned, Arry,) and there are quite a few kings differentiated by regnal numbers (there are five Aegon's!) So my table might look like the following:

CHARACTER (
ForeName
HouseName
Title
NickName
RegnalNumber
PageIntroduced
BookIntroduced
)

The question is: is this complete overkill, or should I use a single "Name" field encompassing all five attributes, even if it's non-atomic? I'm sure it's neater, especially for "King Aegon V "the Fortunate" Targaryen", but it would lead to a lot of fields not being used - many characters have only one name and nothing else, such as "Rorge", "Biter" and "Hot Pie". Is this a problem in databases? Does it matter if fields are left blank in databases?

share|improve this question
    
Also, I've only studied databases and normalisation, I have little to no practical database experience, so I'd appreciate not going overboard with unexplained jargon. –  Leo King May 25 at 11:52

1 Answer 1

up vote 3 down vote accepted

Splitting given names from surnames is not normalisation. What would be normalisation would be if you created a series of tables that contained additional details about some of the columns in your table.

For example, you could create a HOUSE table that contains information about the house other than the name, such as it's catch phrase or home town etc. You might do something similar with TITLE and BOOK.

Don't worry about nulls in your case. Some people are concerned about too many nulls because it can be a sign that your design is not correct or because they are worried about wasting disk space. In your case neither of these concerns is germane.

share|improve this answer
    
Why do you think that the amount of blank space isn't a problem? Where do you imagine it would definitely be a problem? –  Leo King May 25 at 12:21
1  
@LeoKing - Blank space is a problem if you have many, many nulls per row or if you have an enormous number of rows or if you have tiny, tiny hard disks. For a table with a dozen columns and a few hundred rows (or even thousand) the amount of "wasted" space isn't going to be worth creating an elaborate technical work-around. I would say: trade off a few KB of hard disk for much simpler, more maintainable code. –  Joel Brown May 25 at 12:28
    
Okay :). It should be a very small scale database. Do you think it needs more than one table for the purpose? Unless I'm missing something, the only entity is the character. –  Leo King May 25 at 12:29
    
@LeoKing - You could start with one table and then decide later if you want to create tables for additional details beyond the character, such as books and houses. Keeping these potential "foreign keys" separate from the outset will save you retyping your data later and it will simplify querying the data, such as "show me all the Starks". –  Joel Brown May 25 at 12:31
1  
@LeoKing - Yes, precisely. If you have a separate column for house name then you can easily query that way. If on the other hand you kept everything in one combined name field, then you'd be down to using WHERE name like '%stark%' which is always problematic for various reasons. –  Joel Brown May 25 at 15:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.