Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nick
Posting Yak Master
155 Posts |
 Posted - 2002-12-11 : 14:51:37
|
| I'm in the process of designing a database, and one of the tables lists cities. Most of the fields are standard. Name, Population, County, Native Language, etc. However there are about 10 fields that will have long narratives. For example one of these fields is a narrative about what the city is know for. Here is a sample for L.A.Los Angeles is known for Hollywood, movie stars, its beaches, Beverly Hills, movies, and rock & roll. Disneyland, traffic, freeways, The LA Lakers, The LA Dodgers and great food are all part of life in the largest city in the United States.Los Angeles has built its reputation on the glamour of the movies, and most visitors want at least a little of its glitz to rub off on them. Hollywood itself (in northwestern LA) is no longer the movie mecca it once was, but it certainly holds plenty of historic interest. Take a walk down Hollywood Blvd and you'll pass by famous sights such as Mann's (née Grauman's) Chinese Theatre, where more than 150 of the glitterati have left their prints on the sidewalk out the front. Head east along the Boulevard, stepping on those famous bronze stars, and you'll find yourself at the Roosevelt Hotel. Soak up a bit of 1930s ambiance: this is where the first Academy Awards were held in 1928.Just north of the airport, Santa Monica is one of the city's most appealing neighborhoods. Although the beach only comes to life on the hottest summer days, the surrounding area is a very pleasant place to spend an afternoon. The heart of Santa Monica is the 3rd St Promenade, a lively pedestrian mall packed with buskers, movie theaters, bars and cafes. The Santa Monica pier, built between 1909 and 1916, is the oldest pleasure pier on the West Coast. It has plenty of old-world carnival attractions, including a 1920s carousel, and seafood restaurants. The neighborhood is also home to some excellent museums of modern art.Venice pretty much sums up the LA lifestyle. The beach's Ocean Front Walk is a human circus of jugglers and acrobats, tarot readers, jug-band musicians, pick-up basketballers, oiled-up fitness freaks and petition circulators. A hundred years ago, this place was just swampland, until an enterprising cigarette tycoon turned it into a network of gondola-poled canals and dubbed it the 'Playland of the Pacific.' Most of the canals have now been paved over, but the playland atmosphere is hanging in there. It's a great place to shop and an even better place to down a freshly-squeezed juice while the human tide washes over you.From both a performance and design perspective, what is the best way to do this? Should I include each of these fields in the city table, or should I give them their own table and use a primary key to form a relationship between the tables. Any insight would be appreciated.Thanks,Nick |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-11 : 15:24:57
|
| A separate table with one column for the narrative only, and the extra column(s) to link to the cities table. Ten narratives would be stored as ten separate rows. It allows for the most flexibility. If you should want to search all of the narratives it will be much easier to search only one column instead of 2-10 or more. |
 |
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2002-12-11 : 15:34:29
|
| OK, Let me sure I'm getting this right. The cities table will have 10 extra columns, all of which point to the Narratives table? The narratives table will have to columns, and ID and the Narative?Does that sound right? Also after reviewing some of the narratives, I've noticed that they get rather long.. 10,000 characters long, and for international reasons they have to be in unicode? I'm thinking that nvarchar can't handle this size am I right, and is there a solution to tha problem? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-12-11 : 15:40:47
|
| Nick, Two thingsOn the table design, leave the "narritive" fields out of the city table. Create another table called CityNarritives with a key that links it to the city table, and a nText Field. If you have 10 narritives for LA, you'll put 1 record in the City table for LA, and then 10 records into CityNarritives.Does that make sense?A nText field should allow you to have a narritive that is up to 2GB's :)Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2002-12-11 : 15:42:12
|
Right, so I'm designing it as an n-n relationship between the cities and naratives... I'm assuming the nText allows unicode?Thanks for your help!quote:
Nick, Two thingsOn the table design, leave the "narritive" fields out of the city table. Create another table called CityNarritives with a key that links it to the city table, and a nText Field. If you have 10 narritives for LA, you'll put 1 record in the City table for LA, and then 10 records into CityNarritives.Does that make sense?A nText field should allow you to have a narritive that is up to 2GB's :)Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
|
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-12-11 : 15:45:11
|
| Yeah all of the "n" data types allow for unicode.You'll be creating a 1 to many relationship between City and CityNarritives.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2002-12-11 : 15:48:40
|
Right.. I misread your previous post.. Thanks tons!quote:
You'll be creating a 1 to many relationship between City and
|
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2002-12-11 : 15:53:26
|
| I do have one problem with this design. Each Narrative will be associated with a different category. There will be a landmark narrative, history narrative, geography narative, etc.Would I preserve these by putting the relationship between the two tables in the cities table in 10 separate fields historyNarative, geographyNarative... or would I be better off creating a third table with narrative types and including that relationship in the CityNarrative table? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-12-11 : 16:18:07
|
| I would go with the following tables:Cities: *CityID, Name, etc (no narratives)Narative_Types: *NType_ID, DescriptionNaratives: *CityID, *NType_ID, Narative (nText field)(* means these fields are part of the primary key)So you can have as many narative types as needed, and a full text search will only have to check the one field in the Naratives table.- Jeff |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2002-12-11 : 16:20:37
|
| Right on, thanks a bunch. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-12-11 : 21:33:05
|
Would that be 1GB characters since its Unicode? It's enough for Nick, I'm just asking... quote:
A nText field should allow you to have a narritive that is up to 2GB's :)
I understand the field length won't change, but probably only allow storage of 1 billion characters instead of 2.Sarah Berger MCSD |
|
|
|
|
|
|
|
|
Table w/many long narratives.