Beginner assistance

[goatroapr]

Hello. I am having a heck of a time getting started on my database. What I want to accomplish is to create a War Items Database for my Uncle who has an entire room full of relics from different wars. I have built databases before but never to this complexity and with so many tables involved.

Do I have too many tables?

Would the relationships be (i.e. many ITEMS to one NAME / many ITEMS to one WAR)?

Do I need to put all the table ID references in the tblItem? There has to be an easier way to reference them but am drawing a blank.

Thanks in advance,

Jason

 

[Lightwave]

Ok I would say your heading in the right direction.. What I think your struggling with is the many to many relationship. It is confusing when first starting to define relationships but I can see with your structure you are starting to get this. The formal way of defining a many to many relationship is by using something called a Junction Table. I can see from your pdf that you have started naturally coming to that conclusion.

For a minute let me take the liberty of interpreting the pdf in a way that would make sense to myself. I don't exactly know much about war relics but it looks like you are going in the following direction. I suggest four "main" tables and a further "five" junction tables.

Junction tables typically provide joins or junctions between the main tables such that they often contain the foreign keys of the main tables..

So a possible structure

TblWar
PKID
Name
StartDate
EndDate

TblRelic
PKID
Manufacuter
ManufacturedDate
SerialNo
CountryofOrigin
Description

TblPersonnel
PKID
FirstName
MiddleName
Surname
Born
Died
Story

TblPhoto
PKID
DateTaken
Description

From your pdf I would get rid of price and year tables straightaway. There is little advantage in splitting of things like this in the

structural phase.

Now I would make a 5 junction tables.
These are tables where you can link the above tables together. Such that you can have many to many relationships between everything. I see from your pdf you have created a similar one although I think you might struggle going forward with the links you have shown.

My take on these junction tables are as follows.

TblStockJunction
PKID
RelicID
FirstboughtDate
FirstboughtSource
FirstboughtPrice
SoldDate
Soldto
SoldPrice

TblOwnershipJunction
PKID
RelicID
PersonnelID
StartDate
EndDate

TblPersonnellPhotosJunction
PKID
PersonnellID
PhotoID
DateUpdated

TblRElicPhotoJunction
PKID
RElicID
PhotoID
DateUpdated

TblWarOPhotoJunction
PKID
WarID
PhotoID
DateUpdated

This structure should allow you to have multiple people owning a single relic and indicate when they owned and when they got rid of it.(which I guess might be useful in establishing history of the relic)
I imagine the photos are quite important. Any individual photo may have multiple people in it and multiple relics. A relic may have multiple photos of it. But each photo was only taken at a specific time and will have a specific description.

 

[goatroapr]

That is what I was missing Lightwave: junction tables. nice!

I went with your layout suggestions and it looks cleaner now. I even took out the photo table I had because the initial thought was to have individual photos of all his stuff but that would make this thing huge. I did make a country lookup table as the one i had had 238 countries listed in it already. That gives me the dropdown under the RELIC table.

After talking to my Uncle, he also mentioned there are different types of types of relics which is why I put a RelicType in the table. He gave me a general list but he has so much stuff that when we go through it, he may find stuff not listed. I broke down the thought in my head for you with the subs of the items connected at the bottom of the page (RelicType>clothes>uniform, gloves, headgear>helmets, winter, etc). I hope it makes sense.

For instance: the xtblEphemera would have 'Paper, money, letters, stamps' in a row but can you add to the list later on if we find something new?

Would it make sense to make lookups for the different types of Relics and then of the different types of, say, clothing items, or is this a query issue after the tables are built?
Are lookup tables even a good idea?

I may be getting ahead of myself a bit

 

[Lightwave]

G

Good stuff. The main thing was to understand the concept of the junction table and now that you have that I would say that the structure you have is pretty flexible now.

Lookup tables are good to have. Although there a a number of subtle ways of implementing them. But note you can use look up tables without requiring the concept of Foreign Keys and storing the ID

Some pointers on Lookups that I personally use
* Good to use in situations where there are distinct categories
(this was why I got rid of the price lookup price tends to be a gradual scale)

* Can be useful for categorising things into Ephemera's but beware you always find things that kind of are between. Eg the platypus. Mammal or Marsupial?

* Its nice to have a complete scale when using category lookups. Eg everything has a colour but not everything has a season. Medals won't be a summer or winter medal but clothes will. The problem you are already predicting is that you will constantly be adding to the category type (in an ideal world ou would add all the categories at the start) this is the categorys you are thinking of aren't complete.
Maybe in the Ephemera type really restrict it down into 6 categories - Clothes, Weapons, Memorabilia, Equipment, Document, Medals?

* Have you thought of restricted duel categories. EG if you have a Category 1 and a Category 2 and ensure that you have complete range fo the second category as well EG category 2 could be material, Iron / Wool / Steel / Paper etc..
Or maybe a good category would be colour - Black/Red/White/Other..
Like I said everything has a colour but not everything has a season.

Splitting categorisation like this can exponentially increase your effective range ie category 1 * category 2

*Yes you can add to lookups later on. Just make a form on the lookup table by itself. I personally then have a query based on the lookup table to sort everything in some kind of order so that when it is "looked up" by the user it makes more sense.

* Tends to work well when you are categorising thousands of things into tens or less or like countries where there is absolutely no doubt what lookup it fits into. So country is absolutely fine.

*You don't have to use Foreign Key's to store categories. For instance I tend not to store FKID for year I just store the year but use a lookup place in the form and tied back to the field. When it asks what value you would like to store in your table you just say year rather that the PKID of the Year Query that is being used as the lookup.

*Note you should not be using a lookup in the table design but it is better to design it into the form. This is best practice although I've been guilty of doing it at table level before.

 

[goatroapr]

Thanks for the assistance and feedback. This will get me over the hill I was having trouble climbing. I can start building the rest of it now and come back and make additions as needed.