Basic Question on Join

Hi,

I'm pretty much a database beginner and have what I think is a basic
question:

If I have a table which has a 'status' column and I can have say three
statuses: "active", "pending", "inactive". Is it better to break these
out into a different "Statuses" table?

On the one hand it seems to me it would be better to be in a different
table since I'll index this column because I'll be in a where clause
sometimes and I think indexing it would be easier than indexing the
actual words. On the other hand, are joins going to slow queries down
too much? I read something about creating my own sql server 2000
datatype - would this be a good place to do such a thing?

Thanks for your help. (And if you could provide a reason why one
solution is better than the other so I can start understanding better,
I'd be very appreciative.)

Thanks.nick wrote:
> Hi,
> I'm pretty much a database beginner and have what I think is a basic
> question:
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?
> Thanks for your help. (And if you could provide a reason why one
> solution is better than the other so I can start understanding better,
> I'd be very appreciative.)
> Thanks.

If you're storing addresses should you have 50 separate tables, one for
each state?

Zach|||"nick" <nickgieschen@.hotmail.com> wrote in message
news:305cab98.0411191325.29e48efc@.posting.google.c om...
> Hi,
> I'm pretty much a database beginner and have what I think is a basic
> question:
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?
> Thanks for your help. (And if you could provide a reason why one
> solution is better than the other so I can start understanding better,
> I'd be very appreciative.)

Having a seperate lookup table may give you a better performance, testing is
the best way to determine. The lookup table could have an numeric value
that corresponds to active, pending, inactive and then you store the numeric
value in your main table. You wouldn't necessarily have to join to the
lookup table, you could just say WHERE Status = 1.

Using lookup tables can be more flexible for front-end coding because if
you have additions to your lookup choices you can simply edit the table
rather than digging around in the front-end code.

I wouldn't think a user-defined datatype would be useful for this.

hth|||nick (nickgieschen@.hotmail.com) writes:
> I'm pretty much a database beginner and have what I think is a basic
> question:
> If I have a table which has a 'status' column and I can have say three
> statuses: "active", "pending", "inactive". Is it better to break these
> out into a different "Statuses" table?
> On the one hand it seems to me it would be better to be in a different
> table since I'll index this column because I'll be in a where clause
> sometimes and I think indexing it would be easier than indexing the
> actual words. On the other hand, are joins going to slow queries down
> too much? I read something about creating my own sql server 2000
> datatype - would this be a good place to do such a thing?

Basic question? It is actually one that I run into when I'm database design
every now and when, and the answer is not always the same. Mainly it is a
judgement's call. But these days, I tend to go for the table.

I like to first point that when I decide whether to add a table, or to
just have a column with some distinct values, performance has none to with
that decision. More important is the aspect of maintenance, the possibility
to present the value, extensibility and whether the value is important
enough to deserve a table.

Having a single column like:

status chat(1) NOT NULL CHECK (status IN ('A', 'P', 'I'))

has the advantage that it's up little space in the documentation, and
so easier to grasp. But if the value is to be presented in a GUI, the
GUI gets responsible for doing the translation (or the user gets to see
A, P and I. Another issue, is whether you need to add a new value. For
various reasons when we change tables, our standard routine is to take
the long way and reload the data into a new table. A bit expensive for
a changed CHECK constraint.

If you have a column with a lookup-table like:

status char(1) NOT NULL REFERENCES statusvalues (status)

this is more work for me initially, because I have to create an INSERT-
file with the statusvalues, and there is one more table in the database.
(In our case actually two, because there would also be a name table
that gives translations in different languages.). But if I later need
to add a new value, it's just a matter of changing that INSERT-file and
no need to reload the table. And with all strings in the database, the
GUI gets a simpler job.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

basic design question

i am a beginner and any response will help me a lot.

i have 20 types of products,
each product has about 40 fields, most of them are common fileds for all products, but some of them are unique.

the number of the unique fields of all the product types is 75.

the question:
Should i build one products table with 75 fields and leave nulls where a field is not property of a product type?
Or should i build a products table for the 30 common fields and a 20 different tables for each product type extra fields?
Or just build 20 different tables each product type?

other ideas are welcome

you can use Named-pair design technique that is very comon in Software as Service application where we have to maintained the list of customer and some customer can have attribtues that are uncomon to other.

create a table called ProductAttributs that can have column like

1- ProductAttributeID,Attribute Name , Data Type ,IsNull

then Create another table called Product Attribute Value that has column

1-ProductID

2-ProductAttribute ID

3- Value

and you have a base table Product which contains the common fields only

so by this first you can create a set of advance product attributes in Product Attribute Table

then bind your product table with Product Advance attributes in product attribute value so by this you can manage dynamic Product attributes that are not comon in ever Product.

|||

hi., please help me, i am a student, i have basic idea on webprogramming specially ASP.NET, actually im using Visual Web Developer.,

i have a new "complicated" project. i really need help.

1st. the data base, this is the diagram of the data, it is not the actual form but its more like this:

if i will do it in the form of access, it will look like this.

LinkFromLinkToexisting1depleted1fots_brae1cdma_fots1 1111AngelesArayat16800AngelesBalanga27000AngelesBalibago63000AngelesClark16000AngelesDinalupihan0000AngelesHermosa0000AngelesMacabebe64000AngelesMagalang16800AngelesOrani0000AngelesSFP7312600AntipoloAntipolo RSU16000AntipoloTanay42000BacoorBacoor RSU166300BacoorCavite City16000BacoorDasmarinas152100BacoorImus63000BacoorKawit16000BaguioBaguio RLU/ELU1111BaguioLa Trinidad1111BalagtasAngat1111BalagtasAngeles1111BalagtasBocaue1111BalagtasCabanatuan1111BalagtasGuiguinto1111BalagtasIba1111BalagtasMalolos1111BalagtasMarilao1111BalagtasMeycauayan1111BalagtasNorzagaray1111BalagtasPandi1111BalagtasSan Jose DM1111BalagtasSta. Maria1111BalagtasTarlac1111BatangasLemery1111BatangasLipa1111BatangasMamburao1111BatangasSan Jose1111BatangasSto. Tomas1111BinalonanAsingan1111BinalonanBaguio1111BinalonanBalagtas1111BinalonanBalungao1111BinalonanDagupan1111BinalonanLaoag1111BinalonanLingayen1111BinalonanMt. Kabuyao RS1111BinalonanPozzorubio1111BinalonanRosales1111BinalonanSan Carlos1111BinalonanSan Isidro RS1111BinalonanSan Manuel1111BinalonanSan Nicolas1111BinalonanSan Quintin1111BinalonanSantiago1111BinalonanSFU1111BinalonanSison1111BinalonanSta. Maria1111BinalonanTayug1111BinalonanTuguegarao1111BinalonanUmingan1111BinalonanUrdaneta1111BinalonanVigan1111BinalonanVillasis1111

if you would look on the table and the diagram, there are just 2 lavel in the table which is in the diagram has 3 level.

thisis the story about that. the table also has 3 level, look on the Boldwords, if you would look on the many "angeles" there is also not so farbeneath a single "angles" but this time, he is under "balagtas", sothis is the levels, "Balagtas --> Angeles--> (bold names underangeles)" how to know the other names with same levels,

@. lookfor names that are inputed in many times in the "LinkFrom" header thenlook for the same name in the "LinkTo" header. so just look closely inthe table so you can under stand the format.

so this is the question, how can i design my database from access?? what will it look like?

after the database, this is the story for how should the web page operate.

@. assumption: that there will be 3 dropdown menu, each menu correspond to each level.

sofor example, if i choose an item in the level 3 dropdown menu, it willshow a table or info (if you would look in the table above, it will bethe numbers' "1's") for:

1. link from level 2 to level 3.
2. link from level 1 to level 2

so to make it short, will should detect he's original link were ever he was link to.

so if i select a value from the level 2 dropdown, it will only show the link from level 1 to level 2

if select a value from level 3 it will show link from level 2 to level 3 and level 1 tot level 2.

it should trace were he is connected so i think the real key here is the design of the database.

actuallythe table i show to you is the real table but i cut it to make itshort. so there are still some 3 level layer out there in the sameformation as the "Balagtas --> Angeles --> under angeles" intwo columns style.

i hope someone can help me tothis,...please. this day is friday here in the philippines, i willhave no class tomorow, only here in the school were i can work and openthis forum. i hope in monday there will be an answer. thanks in advanceto any person.

please feel free to ask it you want to know any clarification to my post.

SALAMAT PO...

backups explained

Beginner's query.
please explain the OFA (open file agent) or lock rule when databases are being backed up.
I know files cannot be backed up if open, but what about database tables-not metadata, but data.
and what about the images accessed by a database-the reports or the docuemnts-they are backed up separately?
where can i find some basic rules for DB's...Short of DB's for dummies.Databases can be backed up while on-line.
Don't try to copy the .mdf/.ldf files - they won't be restorable probably - see backup database in bol.|||What concequences (if any) does open file agent or perhaps locks in this case have when backups are in operation and records are being updated?

For example on NT a file will not be backed up if open. I know the mdf and ldf files (or is it trn also) take logs, and snapshots for transactions, so that db's can be restored to a past point in time (rollback?). i know that bak files can be copied and used to create a database (restore maybe), but what about in db's?

Or is it simply that at that moment a backup is being written to file, and if the transaction is not fully committed prior to or at that time, it is not backed up, but will be included in the next back up...|||mdf file is the database file
ldf is the log file

bak is the database backup
trn is the transaction log file backup

It is not advisable to restore the db based on the mdf and ldf files - as they may be open at the time of backup. The only way to be sure you can restore is to use the bak and trn files. These will also take care of database locking and incomplete transactions at the time of the backup.|||Any pages updated while the backup is taking place are marked and written again to the end of the backup file.
Enough of the transaction log is backed up to allow a restore. Uncommitted transactions are rolled back at the restore.

The backup will slow down all processes on the server but will not stop any activity on the database.|||thanks all
appreciate your time :)
have just been on a sql 2000 admin course so it all sounds alot smipler now!
cheers