Newb Question on linking tables

[RainLover]

Trachr

I just assumed that this was adding extra work for access when I could just use that list in a form once and t hen access woudlnt have to cross reference again since the correct data would be stored in tblseries... I guess I figured having to look up a seperate record would take more time and processing power then just having to read off a field without having to open a second record then read off the data from there instead.

The type of processing power the computers have these days make performance issues mostly fade away. There are far too many other things to worry about rather than this one very small issue.

 

[Frothingslosh]

Frothingslosh

Just a couple of suggestions on your design.

Why do you use the letters ID.

Honestly? Habit, and since I've used ID in keys since I started way back in Access 2, it makes it easy for me to spot a key at a glance in my own databases. I actually just started adding PK/FK within the past year, mostly as a result of hanging around here.

Would not ConditionPK be just as good as ConditionID_PK but less letters and faster to type. You could apply the same rule for all of your Keys. This will make no difference to performance etc. I just think it is a little tidier.

Probably, but I type quickly enough you're talking about saving something like 1/5 of a second or less per occurrence.

tblPublishers is not following either rule. ID_PK or PK

Doh, you're right. That was an oversight in my trying to get the example thrown together in a quick lull at work. That layout was about 5 minutes of work.

tblStorageTypeID_PK Just does not look right. It appears to be lacking a description like BOX K9 104 which then has the other fields to describe it better like you have. Capacity might be that field which describes the box. eg a Box of 1x1x1 = a 1 Litre Box.

This is mainly because this particular table wasn't discussed all that much. It's basically just to describe the different boxes (or other things) Tracher might use - more suggestion than final table. Capacity was defined by the him as the number of comics that could be stored. I included three dimensions mainly as an example of other data that could be stored, and did it as three fields simply because Trachr is new to this, and I didn't want to hand him a field that might require frequent parsing.

And comments are always good! I've been known to get stuck on one path and as a result miss something that should have been incredibly obvious!

 

[RainLover]

Froth

You are doing good. It is a little different to me but that is life.

 

[Trachr]

Ill change my system to match this... I still don't understand why certain things are necessary but Ill just get used to doing it till I do understand it all lol.

 

[Frothingslosh]

Ask your questions, man! If I can't answer 'em, I'll go tap RainLover on the shoulder. But seriously, the Why is even more important than the What.

And don't forget to put the attachments field in tblComics. I just realized, looking at the image again, that tblStorageBoxTypes should have some sort of Name field for the type, as well.

 

[Trachr]

so basically the priority is to minimize database size... not worry how many processes the db has to use to complete an operation...

For instance if I had in tblseries the publisher name itself when I made the form for that series they db would have to access that record, then read the publisher field so 2 processes basically

This way the db has to access tbseries, goto the volumeid, then access tblpublisher, goto that record in tblpublisher, then read the publishername field.... seems like a lot more steps to achieve the same results

I do however see an advantage in individual table size since that will keep tblseries smaller

 

[Trachr]

since Im redoing a few chunks of things question on how you would approach something.

Say after its all said and done I would like to be able to print off a report of what comics Im missing from a certain volume, also be able to print off between ranges as well in a certain volume

then Id like to be able to do that same t hing for multiple comics so I could form a wishlist of sorts of all the comics I need

Would that be done with a new tbl to remember ranges and such or would that all be done with a report?

 

[Frothingslosh]

Nope, you'd do that with queries and reports. It's not hard to generate a list showing all owned comics from a specific series with a specific volume number. There's no need for a table to store calculated values - instead, you just generate them on the fly.

As to your other post, basically you're right. Until a database gets INCREDIBLY large, you worry more about saving space than processing power. The differences you're talking about in processing are honestly measured in the thousandths of a second of processing time in a database this small, and what little overhead you generate are more than made up for with the flexibility and adaptability the relational model gives you.

Now if you were talking about pulling thousands of records in a hundred-field query out of a database with a million records, processing speed becomes an issue, but there are other, more effective ways to free up cycles than combining tables.

Also, look at it this way: with a properly normalized database, if you change an entry on the 'one' side of a one-to-many relationship, you just changed it for every record related to that item. If you had combined the two tables into one, you'd have to go through that table and change the value for every entry in the table where it appears. That alone would chew up more time than the processing of proper relationships.

Normalization also reduces errors. If you put a publishers field in tblSeries, you run the risk of having "Marvl", "Marvell", "Mravel", etc, in addition to "Marvel". Trust me, no matter how accurate you try to be, errors WILL happen, and part of proper design is doing everything you can to minimize the opportunity for error.

 

[Trachr]

what about all unowned comics from a specifc series and a specific volume number? lol that would be the ideal way to have a wish list

and ya I guess I can see where that would be better... just have to get used to the idea lol

 

[Trachr]

so how does this look?

 

[Frothingslosh]

Honestly, there's nothing saying you can't include a wishlist or wanted table or something like that.

Relationship chart looks good - just keep RainLover's suggestions in mind, too. The main reason for the xxxID_PK and xxxID_FK is my own little idiosyncrasy, so don't feel you HAVE to use the naming convention I use. The extra fields you added all appear to be on the best tables for them. My only suggestion would be that it wouldn't hurt to have a name (BoxTypeName ?) in tblStorageBoxType, but I suppose the description field could double for that.

 

[Trachr]

so if I wanted a wishlist the best route would be a new table for it?

 

[Frothingslosh]

Actually, no.

Think of what you would be tracking for your wishlist, and compare to your database. Remember what you read about regarding normalization.

I can give one hint (highlight to read): you can create your wishlist with the addition of a single field to a single table.

 

[RainLover]

Froth

It is good to see someone who has such a good grasp on Table design. This is not to say you have got this 100% correct as you do not know exactly what is in Trachr's mind. But it is a very good start and can be sorted later if things change..

Having a print out of the relationships is great. Especially in A3 and pinned to the wall.

I would like to see the type of relationships you have. I never tick Cascade Deletes or Updates.

Trachr With your naming try to use Camel Hump.

tblCrossOverList is better than tblcrossoverlist

The primary Key usually has a name similar to the Table name. tblStorageBoxList would have StorageBoxListPK.

tblgradelist has ConditionID_PK. Makes no sense to me. Remember you have to come back to this Database in 6 Months or longer time. You cannot reply on memory. This is a good reason for a good naming convention plus heaps of notes when you start coding. (Within reason that is)

http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[RainLover]

Do these Comics have their own Name. Like "Superman Comes Back" which is number 14 of the MidShadow Series.

Maybe you have this under a different heading. BTW I know nothing about Comic Books other than what I have learnt on Big Bang.

Comics are people not books. Just had to throw this useless bit of info in for you to play with.

 

[Frothingslosh]

*steps back from ground zero*

 

[Trachr]

Froth as for the wishlist, honestly I was considering adding two fields to tblseries call them say wishliststart and wishlistend that was one of my solutions before I asked on here


and rain, ya the title may be a good idea, not all have em but some do, I honestly dont ever notice em but if I ever give this system to someone else titles may be important to them.... and as for comics are not books... sure they are, I have a nice library of books I collect too a few signed asimovs even

 

[Frothingslosh]

The option I was looking at was simply adding an 'Owned' yes/no field in tblComics. You can, as a rule, assume that if you've entered a specific comic's information but don't own it, it's on your wishlist.

 

[Trachr]

So, Out of curiosity...

Since my current setup bunches all the volumes in the same series... which has merit but if I wanted a way to track for informational purposes the start and end of each volume would I do that with an additional table... in that tbl have say seriesID, then Volume, then Start Issue, end issue, and maybe and ongoing yes/no field

Would that work? It would in theory allow me to check any volume I have added for a series and see how big that volume is.

 

[RainLover]

You could have a Table that completely describes each and every Comic, Who published it, When is was first and last published, Series Number etc.

Then if you want to use this stored information you can report on which ones you have or don't have. All the ones published by Smith and Co. etc.

Froth, what do you think. If I am understanding the requirements correctly then I believe this way to be correct.



This would mean a little bit of a rewrite but not much. This is why we get our tables correct first rather than designing too many forms and reports. It is not easy to for see what other ideas someone will come up with.