Recommended schema websites?

[GopherUK]

Hi,

Just wondering if there is a website out there that gives professional DB schemas? I am sure I saw one here a while ago but I cant seem to find the thread for it.

Any help would be greatly appreciated.

 

[GopherUK]

Thanks very much guys. I am still studying hard and I think I am close to getting the feel for it.

 

[GopherUK]

I am beginning to grasp the difference in "quick and dirty" data modelling and proper data modelling.

Working with a sales database scenario, am I right in assuming that proper data modelling would include, for example, tables to allow multiple address for customers etc and tables to allow multiple prices for items? And that these would be indexed by a composite key that would include the date to protect against duplicates? For pricing, I fully understand the concept of derived data but if a company wishes to keep a record of product pricing (which is clearly very valuable when it comes to business/data analysis), then it would require a static price history. So the price for a certain item would be derived from a junction table with a unique composite index of date+item ID? Would that be correct? And I would imagine you would have some kind of yes/no field in price table to state whether or not that was the active price and use this as a default, allowing users access to inactive prices but warn them before doing so (in case of backfill exercises)?

 

[GopherUK]

I am currently adding a table of ISO specified country names and code as a reference table on lookup. I assume there is no need for an autonumber when I can just use the country name as the PK and index it with no duplicates?

So effectively you only need to use Autonumber when you cannot guarantee uniqueness for a PK, is that correct?

 

[jackd]

Sounds good - ISO sets country names, and adjusts as needed. There is a list of changes available as well.

This site appears to have latest codes.
https://github.com/lukes/ISO-3166-Countries-with-Regional-Codes/blob/master/all/all.xml

 

[GopherUK]

Thanks mate.

So I have got the basic concept about PKs right then? You only really need to use Autonumber when you cannot guarantee uniqueness by other means?

Odd thing about learning Access is that I always thought it would be harder than Excel (I freelance Excel) but it's actually not at all I dont think - it's just less untuitive. Seems once you get the general idea it all falls into place.

 

[xenou]

I am currently adding a table of ISO specified country names and code as a reference table on lookup. I assume there is no need for an autonumber when I can just use the country name as the PK and index it with no duplicates?

So effectively you only need to use Autonumber when you cannot guarantee uniqueness for a PK, is that correct?

Yes and no. Purists will still use an arbitrary key value (aka surrogate, artificial or identity keys). The reasoning is that country names can (and do change): Burma/Myanmar for example. And I believe ISO does allow for changes in its codes. It's a low risk item. I can see how it would be convenient to use the ISO codes as the primary keys, but you *might* need to do some cleanup depending on political events in future years.

You can always use an autonumber primary key and *still* index the candidate key (the country codes) to ensure uniques. There's a school of thought in Access design that says that all primary keys should be artificial keys. I don't fully subscribe to that theory (yet) but 90% of the time in practice that's what I do.

 

[GopherUK]

Ah, OK. Thanks a lot, very useful theory.

But would I be right in saying that on a junction table for customer addresses, it would be safe to your a composite of the date when the customer moved to the address and the postal code would be safe enough to use as the PK as the date (unlike the ISO country name code example) is always going to be static as will the postal code? So these two fields can be indexed to no duplicates and set as the PK?

 

[GopherUK]

Or perhaps it's best to just always use an Autonumber PK and not try to be clever about it?

 

[jackd]

Postal codes in Canada can change - not too often, but in some new subdivisions, or rural areas that become more populated new postal codes are created. I've forgotten some numbers , but it could be something like a 1000 change each year and 3000 are created??? Maybe a bad guess it's been a few years since I worked on systems where that was an issue.
For info, we created a new territory, Nunavut a few years back and all of the affected area and postal codes were revamped/reviewed/adjusted.

As for customer - if you have a PK for customer, you can have addresses with customerid and FromDate , ToDate to maintain a customer address history.

It's hard to find an example where autonumber PK won't work --I'm sure someone will have an example, but I would say you're pretty safe with autonumber PKs.

 

[xenou]

I imagine there might be some reason to use artificial keys 100% of the time, but so far I've found all is well either way (I usually use autonumber keys, however). Basically it's your pick. I avoid fields in primary keys that are very complicated: aim for simple and short IDs, if text than simple A-Z stuff without punctuation, spaces, or special characters, or numeric integer values.

Are you using a junction table because you want to keep addresses for different dates for the same customer (a time-oriented table that keeps track of changes in addresses).