database design questions

Hi,
I designed the database and there are two points I'm not sure that my design was correct:
1) In my search engine (in interface) i have an expression builder and user can save the expression he created. In this case don't chreate view in database, but I have a table named "Expressions" which saves expression name and expression. The reason I made it was: If I create view it doesn't make db to work faster because the select query in view runs every time when view was opened, but it makes my code more generic . But isn't it a design error?

2) I save 3 reserved fields in each table, and I don't know if I'll use them some day. The reason: adding a column to database is heavy operation. But isn't it a design error?

Thank you for adviceDear Yulian

Pre-creating the SQL expressions as views will boost up performance, instead of recompiling the SQL expression every time its executed the view is already compiled and execution time is therefore shortened. however, adding and removing objects (views in your case) to the database by multiple users has the potential to become an administrative disaster therefore I would recommend NOT to create views and keep doing what you already have done.

The question weather an operation is heavy or not is only important if you perform it frequently or its so heavy that executing it actually stops the server for a while. adding columns is neither of these and keeping extra columns is therefore not required, furthermore what names did you give these records? having records named "rec1" is going to make your life very difficult one day, dont do it.|||Dear Idba!
Thank you for you clear and reasonable answers. I have one more question:
in one table I have a field "comments", I estimate that it won't contain text that is too long, that's why the datatype varchar(8000) and not text. It gives me a possibility to use "DISTINCT" in queries, while I can't use "DISTINCT" in text fields. But isn't it a design error?|||Never use TEXT datatype if the character number for the field is less than 8000.|||Actually, you can get distinct values from TEXT/NTEXT/IMAGE fields.

Database design help pls-Landuse

Trying to help a friend design a database for a new project. Trying to figure out entities i need. This will be use on web project, interface will be link to geographical map showing all State in US.

please advise
Thanks

Here's the business infor he gave me

Landuse is a public service network of county and city municipal databases designed to present geographically indexed FACTS for online Internet distribution during the busy and competitive decision-making process.
FACTS are an acronym for: Fast, Accurate, Comprehensive, Timely, Systematic.
FACTS are abstracted; geo-indexed and made available for Internet distribution by Landuse. This daily updated public information service is an important analytical research tool for responsive government agencies and the general public. It is worthy of your inspection and trust
Landuse is a simple and unique map-driven application used to bookmark nationwide real estate databases with menu-driven Township Locator Maps that will visually identify regions, tracts and/or parcels of property relative to pubic information abstracts of record.
Once you have selected a state indexed with Township Locator Maps, Landuse is a four-step drilldown procedure:
Step One is the Township Locator Map, a copyrighted master plan for identifying each and all of the townships in a particular state with four characters [0000].
Step Two is selecting one or more sections; each surveyed to contain 640 acres (more or less) and is numbered, one through thirty-six, within the selected township [01 to 36].
Step Three will produce a selected section divided into four-quarter sections lettered with the NE [1], NW [2], SW [3], SE [4] containing 160 acres, (more or less).
Local county tax assessors prepare property tax maps annually. Each parcel is identified by an assigned tax map parcel identifier, which is our key to index and attach public recorded documents maintained in county courthouses and news releases published by local county newspaper press organizations.
Step Four is a Landuse dual-purpose menu for access to Docufiles (courthouse) and Profiles (newspapers) referenced to the subject property. Daily public information is indexed to the parcel identifiers for access by member associations, government agencies, and corporate sponsors. Upon receipt of daily public information, the data is made available for Internet service, 36 hours later, on a daily, 24-hour, seven-day basisYou really need to talk to your friend about what they've got in mind... They are WAY off on some of their basic assumptions already. If you designed a database to represent land use the way that text describes it, the mis-match between what you store and the real world will be so bad that there will probably be no real use for what you've created.

The MAPS project done for NOAA should be public information by now. They tried to do almost exactly what this seems to be doing, for the purposes of coordinating weather data and alerting the responsible authorities (to remove the dependancy on Weather Reporting Stations for the dissemination of alerts). That project is still underway as far as I know, with ten years of development without a consensus on how to map satelite observations and WRS (Weather Reporting Stations) to responsible authorities!

Until you get a model that more closely resembles the real world, pretty much any effort you put into the project will be wasted.

-PatP

Database design for status field

Hello,

I have a database design question. I want to store a status in a table. In the ASP.NET interface for the user this status will be adapted to the language of the user.

This is how it would look like:

StatusID 1 = "yes" for English, "oui" for French, "ja" for Dutch
StatusID 2 = "no" for English, "non" for French, "neen" for Dutch
StatusID 3 = "error" for English, "erreur" for French, "fout" for Dutch

I don't want to do the translation in ASP.NET and it should be done in one query.

How would it look like in the database design.

WimVM, how about this,

if you are asking about how to design a table or tables, you could have one table (status) like this:

statusID is you primary key and three additional fields (english, french, dutch)

so for instance if you have an english speaker your "yes" would be returned by

declare @.statusintset status = (getYourIdFromApp)select english from status where statusId = @.status

hope this is helpful -- jp|||

Hello,

Thanks for your reply. This would indeed do it, nice and easy. What I forgot to say was that the language is set by an ID, LanguageID. This holds the value 1 for English, 2 for Dutch, 3 for French and 4 for German.

I would need a way that I have more flexibility when I add or disable a language. In the above example you need to know the position of the value linked to the value. Sorry for not informing you about this in the first place.

Thanks

|||

WimVM:

Hello,

Thanks for your reply. This would indeed do it, nice and easy. What I forgot to say was that the language is set by an ID, LanguageID. This holds the value 1 for English, 2 for Dutch, 3 for French and 4 for German.

I would need a way that I have more flexibility when I add or disable a language. In the above example you need to know the position of the value linked to the value. Sorry for not informing you about this in the first place.

Thanks

I still have not found a solution for this. It would be great if somebody could help me with this. Thanks.

|||

WimVM:

Thanks for your reply. This would indeed do it, nice and easy. What I forgot to say was that the language is set by an ID, LanguageID. This holds the value 1 for English, 2 for Dutch, 3 for French and 4 for German.

I would need a way that I have more flexibility when I add or disable a language. In the above example you need to know the position of the value linked to the value. Sorry for not informing you about this in the first place.

Hi Wim, I have some suggestions.

Add a new table to your database called Languages. This table will have 2 fields: LanguageID and LanguageName. The data would look like this:

LanguageID LanguageName
1 English
2 Dutch
3 French
4 German

Add another table called Statuses. This table will have 2 fields: StatusID and StatusName. This table would not necessarily be used in queries, but could be used to store metadata about the Statuses. The data would look like this:

StatusID StatusName
1 Yes Status
2 No Status

Add another table called StatusTranslation. This table will have 3 fields: StatusID, LanguageID, and Translation. The data would look like this:

StatusID LanguageID StatusTranslation
1 1 yes
2 1 no
1 2 ja
2 2 neen
1 3 oui
2 3 non
1 4 ja
2 4 nein

Now, assuming your main data table is called MyTable. This table might look something like this:

ID Column1 Column2 UserLanguageID StatusID
1 Value1 Value2 2 1
2 SomeValue SomeValue2 1 2
3 Value1 Value2 3 1

The query to pull out the status data, translated for the user's language, would look like this:

SELECT
ID,
StatusTranslation
FROM
myTable
INNER JOIN
StatusTranslation ON myTable.StatusID = StatusTranslation.StatusID AND myTable.UserLanguageID = StatusTranslation.UserLanguageID

And the result of that query would be:

ID StatusTranslation
1 ja
2 no
3 oui

This has made some assumptions, such as that you have the UserLanguageID in the same table as the StatusID. You'd need to adjust the queries and data tables to fit your situation, but perhaps this will help get you started.

For some background, you might like Alister Jones' (SomeNewKid) blog post:Localizing the Content, which lead's to Karl Seguin's article:Creating multilingual websites - Part 2.

|||Thankstmorton!