Access question related to tables.

I have a pretty advanced structure of tables (AKA entities) that have relationships for my current database project. I am having some difficulty determining the correct setup for this database. Any suggestions and feedback would be wonderful, in order to direct me in a more logical direction. Here is the problem.

The database is for a horse breeding ranch and there are two kinds of horses: horses that are owned by the farm and customer horses. For the most part they have the same characterists (and are currently as one "Horse" table). There are, however, several entities(tables; such as Show Results, Pedigree) that are associated with only farm horses and an entity that is only associated with customer horses(Imports). Should I somehow split the Horse entity? Am so confused about this part of database! I'm only posting this particularly odd question because I have no other place to go!!!

------------------
~ And that is my humble opinion.

When I was a boy I was told that anybody could become President. Now I'm beginning to believe it. - Clarence Darrow

The greatness of a woman’s power is measured in the size and amount of the balls she clutches in hand. -Me

"Tink"
1 GHz Tbird on A7V, 384MB PC133 RAM, Samsung 19" 900IFT, Radeon 64MB DDR VIVO, SB Live! MP3+, 42.9GB IBM HDD, Plexwriter 8/4/32, 3C905CTXM nic, Intellimouse Explorer

Here's what I would do.

And here's the method to my madness https://www.sharkyforums.com/images/.../2005/08/2.gif
Make a "Horse Types" table so you can relate horses to a given Horse Type (Owned, Customer, etc).

Make a Shows and Show_Results Table, so you can keep track of Shows, and then relate horses to the Shows Table, and store their results.

The "Pedigree" and "Import" attributes sound like "yes or no" type sitautions to me, so I would set them as bit flags (true/false,yes/no,1/0) in the Horses Table.


Make sense? From the limited information you gave us, that's how I would attack it. Others might take a different approach. There's no one right way, but some methods are better than others.

Quote:

---

Originally posted by Grizzly:
And here's the method to my madness https://www.sharkyforums.com/images/.../2005/08/2.gif
Make a "Horse Types" table so you can relate horses to a given Horse Type (Owned, Customer, etc).

Make a Shows and Show_Results Table, so you can keep track of Shows, and then relate horses to the Shows Table, and store their results.

The "Pedigree" and "Import" attributes sound like "yes or no" type sitautions to me, so I would set them as bit flags (true/false,yes/no,1/0) in the Horses Table.


Make sense? From the limited information you gave us, that's how I would attack it. Others might take a different approach. There's no one right way, but some methods are better than others.

---

First I would like to congratulate and thank you for the best reply i have ever received in SharkyForums history. Thank you so much... unfortunately, I am admitedly not familiar with these methods.

I like the bit flag idea and tried to impliment it for a different aspect of the same database, but I was unsure of the correct way to use it as far as entity relationships go? In other words, how do I connect the bit flag from the parent entity to the corresponding child entity? Just connect relationship line?

Second, I am not understanding how creating the Horse_Type entity seperates my horses into two subgroups if (as drawn in your great diagram), the Horse_Type entity is not related to Show_Results... I had a piss-poor professor in database design class... kept talking about normalization... something my logic is trained beyond.

If you could explain these two techniques a little more for me, not only would I be even more grateful for your help, I'd feel comfortable in moving forward. Thx.

You're welcome https://www.sharkyforums.com/images/.../2005/08/2.gif

I see where your confusion lies...and I'm not sure if I'll be able to really effectively explain this....but I'll try.

Let's start with Horses & Horse Types. These two tables are related by Horse_Type_ID. Imagine you have 2 Horse Types:

ID Title
--------------------------
1 Customer
2 Owned

Now imagine you have 2 horses:

ID Type Name
--------------------------
67 2 Starling
83 1 WindRunner

Now as you can see....you have 2 horses...in the same horse table, which belong to 2 different horse types. This is how you store 2 different types of horses in the same table. Make sense?

As far as the bit flags go... from what I can tell you don't have a firm grasp on the idea of entity relationships. With databases....when you relate 2 tables to one another...you can imagine them "welded" together as one table. When I relate Horses -> Horse Types, via the Horse_Type_ID...than all related data in Horse Types Table is made available to me. You don't need to map relationships for every field.

Additionally, Show_Results never has to be directly related to Horse_Types. Show_Results is indirectly related to Horse_Types through the Horses_Table.
Like I mentioned above, when you define a relationship between two tables via a Primary Key, than all related data in those tables become available to you.


Imagine the Shows_Table

Show_ID Date Title Desc
------------------------------------
123 3/2001 MarchShow ...
903 6/2001 JuneShow ...

So lets say "Windrunner" places 2nd in the March-2000 Horse show. We would then make an entry in the Show_Results table like this:


Results_ID Horse_ID Show_ID Resul ts_Place Desc
--------------------------------------------
098 83 123 2 ...

That would be all the data you need to enter...now, like I said I'm a piss-poor teacher, and I'm not sure if this clears anything up for you...but hopefully it does https://www.sharkyforums.com/images/.../2005/06/5.gif You might want to go read about normalization a bit on the net...a good article might be all you need to

wrap your head around these concepts.


[This message has been edited by Grizzly (edited December 17, 2001).]

I think you misunderstood me a little bit, which is okay because it is always better to undershoot when teaching someone, rather than overshoot. https://www.sharkyforums.com/images/.../2005/06/5.gif

I fully understand how relationships and normalization work. In fact, my head is so ingrained with normalization that I have trouble viewing the different normal forms because I initially see the data already in 3rd Normal Form. Don't get me wrong, your further explanations HAVE helped.

What it is that I am having trouble with the bit flags is why do I need them? For querying purposes? I can store the data in the subordinate entities even if I don't have a horse_type entity, correct? I mostly want to eliminate data redundancy and make sure that I'm not going to run into problems on down the road due to the fact that all the horses are stored in the same entity, even though many of the subordinate entities only apply to one or the other of the "types" of horses. So, if adding this Horse_Types entity eliminates some sort of problematic thing, what might it be? I will certainly impliment it if something exists.

Furthermore, I just want to clarify my understanding of the bit flag operation. By placing a bit flag in a parent entity, what must be included in the child entity? This field is associated with what field in the child entity, and how? Does it determine "Only" that the parent entity does indeed contain a record in the child entity for purposes of acknowledging this from a user perspective, or does it serve some database relationship role that i'm not seeing?

I'm sorry if I seem to be asking redundant questions, I just REALLY want to rule out any possible problems and understand what it that I'm doing here since my professor failed to teach me anything new. I so very much appreciate your willingness to help.

I really don't think I follow your questions here...but I'll try and respond the best way I can.

Breaking out Horses into seperate tables, depending on if they're "Pedigree" Horses, or "Import" Horses is simply bad design. You pretty much need to think of this in an Object Oriented way. A "Horse" is an object, and as an object...Horses have certain attributes. So we create a table for all "Horses," and define attributes for our Horses in the Horse Table, or potentially in related attributes tables.

By having a "Horse_Type" lookup table, we can define "n" number of horse types, relating to the Horse Table.

The real key to this method is extensibility. By having a Horse_Type lookup table, we can very easily add new Horses with new Horse Types to our database.

For example:
Say 1 year from now there's a new "Horse Type," for our puporses we'll call them "Yellow Horses." Ideally, we could make a single entry in the Horse_Type table, and then be able to add as many "Yellow Horses" to our "Horse Table" as we need. Very little query or code modication will be necessary.

But if you decided to take the route of making different tables for each Horse, than you'd be forced to create a *new* table for Yellow Horses...which would more or less have the same fields as other Horse tables, but with a few minor differences. This would then require you write some new queries to pull data from the new horse table, and write new code accordinfly to display Yellow Horse data. It becomes increasingly more difficult, and messy, as each new Horse Type comes into the picture.


So the real key with keeping all Horses in the same table, is extensibility. The ability to have your database / application grow as needed, quickly...and easily. That's what good design is all about. Now understand that I put no more than 5 minutes of thought into my above diagram...and had I known more about the subject or the application I'm sure I would've done some things differently. But take this concept and apply the best way you can and apply it accodingly...sound design saves everyone from headaches in the long run https://www.sharkyforums.com/images/.../2005/08/2.gif

AWESOME! You elimintated any doubts I had about creating my database in this way. I like it. https://www.sharkyforums.com/images/.../2005/08/2.gif I am already hard at work putting this method of design to work for me.

If you're interested in more information, here is the farm's website .

[This message has been edited by Nephalim (edited December 18, 2001).]

Would it cause a problem to have two associative entities based on the same three parent entities? I would combine the two, but they are independent of each other. For each of one of the two associative entities, one record may exist in the same circumstance that many exist in the other, and vice versa. To elaborate, the three parent entities are Show, Horse and Rider, and the two associative entities are Show Results and Awards. I don't see any problem with this, but again I'd like to clarify. https://www.sharkyforums.com/images/.../2005/06/5.gif

Once again, I'm still not sure if I understand what you're asking here https://www.sharkyforums.com/images/.../2005/08/2.gif I think we're have a hard time communicating here...because I've never taken a database class. I'm having a hardtime decoding your textbook words up there https://www.sharkyforums.com/images/.../2005/06/4.gif But from what I can read in your last message....you're trying to do something like this:


Click here


That kind of layout works fine, assuming the following relationships:

• Horses => Horse_Types -- 1 to 1
• Horses => Awards -- 1 to MANY
• Horses => Riders -- 1 to 1
• Horses => Show_Results -- 1 to MANY
• Shows => Show_Results -- 1 to MANY

1 to 1 meaning: Each(Horse), has only 1 (Rider)

1 to MANY meaning: Each (Horse), has many Awards

So, my interpretation of these relationships might be a little skewed since I don't have a full understanding of this Horse Ranch....for example each Horse might have *many* riders. If that were the case than you would remove "Rider_ID" from the Horse table, and place "Horse_ID" in the Rider Table. Make sense?

One relationship not present here is a MANY to MANY relationship. This is a special case, and a good designer would then create a 3rd intermediary table to establish relationships between the two. But since I don't think we have any MANY to MANY relationships here, I'm not going to get into that one https://www.sharkyforums.com/images/.../2005/08/2.gif

Did this help at all?


[This message has been edited by Grizzly (edited December 19, 2001).]

Oh, I apologize Grizzly. I thought you were familiar with these ghastly terms that I've been wading through... I can now safely switch back to normalcy. So to get back to my question, what I mean by associative entity is that they tie in other entities because they "associate" with them. For example, Awards "associates" a show, a horse and a rider together to form an Award where the rider and the horse both receive the same award, which happens to be a result of the show. To further explain, here is my entire(geez, the whole project is going public https://www.sharkyforums.com/images/.../2005/06/6.gif ) table relationship diagram: Tables. Skip the crap and type "tables.jpg" in the address line... angelfire is on crack!


[This message has been edited by Nephalim (edited December 19, 2001).]

Ah, well I can't see that image because of Angelfire image security. But I follow what you mean.

Now...my first inclination is that an "Awards" table would be 100% redundant to the Show_Results table? Or are you replacing "Show_Results" with Awards?

I edited my other post with directions on how to get around the image security...
As I said before, Awards and ShowResults must be separate tables. However, Rider, Horse and Show will all be connected to both tables... see the diagram for more information.

------------------
~ And that is my humble opinion.

When I was a boy I was told that anybody could become President. Now I'm beginning to believe it. - Clarence Darrow

The greatness of a woman’s power is measured in the size and amount of the balls she clutches in hand. -Me

"Tink"
1 GHz Tbird on A7V, 384MB PC133 RAM, Samsung 19" 900IFT, Radeon 64MB DDR VIVO, SB Live! MP3+, 42.9GB IBM HDD, Plexwriter 8/4/32, 3C905CTXM nic, Intellimouse Explorer

Also, I'd like to run an AppendQuery the first of every month. How do I do this? Especially if Access probably won't be running 24/7???

Quote:

---

Originally posted by Grizzly:

• Horses => Horse_Types -- 1 to 1
• Horses => Awards -- 1 to MANY
• Horses => Riders -- 1 to 1
• Horses => Show_Results -- 1 to MANY
• Shows => Show_Results -- 1 to MANY

---

Actually, I think it would be something like this:

• Horses => Horse_Types -- MANY to 1 <-
• Horses => Awards -- 1 to MANY
• Horses => Riders -- MANY to MANY <-
• Horses => Show_Results -- 1 to MANY
• Shows => Show_Results -- 1 to MANY

A definition-list table always has one-to-many relationship, as in Horse_Types. One type has many horses, otherwise it wouldn't be any point in having it.

Horses-Riders, I guess a rider could ride many horses, and a horse could be ridden by many riders. Therefore you would need, as you said Grizzly, a new relation with two foreign keys, the primary key from horses and the primary key from riders. A sort of who's using what list.

About the awards, there could be a many-to-many relationship there, with a year as an identifyer (Nicest-horse-99, Nicest-horse-00 and so on), but I dont't know about those business rules.

Always make a good data model first, you will get in trouble later if you don't. Ahh, the pleasure, the pleasure of database modelling. https://www.sharkyforums.com/images/.../2005/06/5.gif

Don't over-normalize... I knew some DBAs that loved to do that, and it became a nightmare...

Basically, if you have a choice of values for any particular field, break it out into a seperate table, then link it by an ID. If the field is totally free-form, it is harder to decide if you should seperate it or keep it in the main table.

For instance, lets say we have a category structure...

category
-----------------
cat_id integer
cat_name varchar(255)

catrel
-----------------
cr_id integer
cr_cat_id integer
cr_parent_id integer

This system makes the data very clean and easy to work with, given sub-selects, or you could loop. cr_parent_id is a cat_id or null if top-level, the rest are fairly obvious.

Another system that works well is putting everything in one table... But, it often causes speed problems due to the fact that the entire record is longer!

category
--------------
cat_id integer
cat_parent_id integer
cat_name varchar(255)

So, due to this example, it is obvious that the less you have directly in a record, the better... Therefore, you need to find a balance, but here is an idea...

horses
-----------------
hrs_id integer
hrs_name varchar(255)
hrs_own_id integer
hrs_type_id integer
hrs_stall char(10)

horse_award
-----------------
awd_id integer
awd_hrs_id integer
awd_type_id integer
awd_level integer
awd_date timestamp
awd_text text

horse_owner
-----------------
own_id integer
own_name varchar(255)
own_address varchar(255)
own_phone varchar(255)
(... etc)

horse_type
-----------------
type_id integer
type_name varchar(255)
type_tpgrp_id integer

tpgrp
-----------------
tpgrp_id integer
tpgrp_name varchar(255)

.
.
.

etc

This is a fairly heavily normalized idea, and is likely not even what you need, but, this is my system for designing databases... My naming convention is a bit odd, but it comes from my years working with high-end IBM products that use IBM schemas. The field naming is my variation on the IBM style, but slightly looser. They often use 2 or 3 character table identifiers, then a field name. The downside is that there is no right-side convention, so you are not always sure if it is an id (relating to another table), or if it is a field with data in it... With my system, I just look for one or two underscores '_', and if I see two, I know I need to get the data from another table. And I use the same field name to make it easier -- hrs_own_id is a value from own_id... very easy. But, use your own convention/style.

Just my $0.02...

------------------
Founder of the Sharky (Ultra) High-Resolution Club[SHRC] [SHRC:ETCH]
[SGC Ultimate pass... Don't read anything I post unless you want a headache]
SF Linux Hippie Club Vice-Vice President [Card]
America Online Doit Mourir
Everything you ever wanted to know and see about e-dawg...

Oh yes... sometimes you will want to use an extension table...

My convention names them the same with _ext on the end of the table name, and _ext_id for the id rather than _id...

You usually use extension tables for large data fields that are not used very often... They are 1:1, so you should never have two with the same id... which is why the id for the extension table is the same as the id for the table it is extending.

example
--------------------
ex_id integer
ex_text varchar(255)
ex_number integer

example_ext
--------------------
ex_ext_id integer
ex_massivetext text
ex_blob blob
ex_clob clob
ex_othermassivejunk blob

So, now when you retrieve a record from example, you don't retrieve all the massive stuff that is in the ext table... plus, it is the safest way to update a table! Add a field to the extension table and make sure you always use SQL statements that specify the fields when using the ext table, and you can use the extension table more freely, since you know it will never return a large record, and the structure will not be changing.

Just another $0.01 from the mess that is e-dawg's mind.

[This message has been edited by e_dawg (edited December 31, 2001).]