Total noob question

[claptrap]

I am trying to learn about databases and at the moment starting to read general stuff about multidimensional database design. The trouble is, I don't even know the basics about databases, like what is natural key (same as primary key made of single components?) or surrogate key... Could anyone explain the following paragraph (we are talking about star schema here):

- "Another important feature is that all natural keys are replaced with surrogate keys: this means that every join between the fact & dimension tables is based on surrogate keys, not each surrogate key should have a generalised structure. i.e. based on simple integers. The use of surrogate keys allows the data in the warehouse to have some independence from the data used and produced by OLTP systems. For example, each branch has a natural key, namely the branchID and surrogate key branchID."

I cannot see any difference between this surrogate key and foreign key in fact table....

and I had the impression that the data produced in any data model should be wholly independent from the programing, OLTP etc? So the line about surrogate keys not always based on simple entegers must give a clue?

[SkinHead]

If Your Experience With Databases Is As Limited As You Say, It Seems Your Rather Jumping In @ The Deep End.

Maybe Focusing On Some Of The Fundamentals Will Provide A Better Starting Point.

There's Umpteen Books For Database Beginners (Amazon.com)

Google "Database Design" For Lots Of Useful Info & Guidance

[claptrap]

Thanks for the advice, any suggestions of a one that will explain more about creating customised input forms, validation rules, what all those parameters at the bottom of field boxes are in Access etc?

I am reading a book called "Database systems, A practical approach to design, implementation and management" by Thomas Conolly and Carolyn Begg, which is mostly easy to understand, only it doesn't teach so much about actually making one, (especially in Access).

I am hoping to create a relational database for my little customer care business (part-time: not big enough to pay someone else to do it) and which I can query various purposes.

I also want to create one for my "library" so I can search not only by author, title or genre but also by bits of text or keywords. I don't expect to be able to use heuristics, though.

[SkinHead]

Firstly, I'd Suggest Microsoft SQL Server Expess, Rather Than Access. It's Free & Downloadable From http://msdn.microsoft.com/vstudio/express/sql/

It Allows You To Generate Relationships Between Tables & Create Constraints - Built In Mechanisms For Enforcing Data Integrity.

Aside From Anything Else, It's More Robust, Powerful & Easy To Use.

Check Out Amazon.com For Suitable Books For Beginners.

You Mentioned Customised Input Forms. I Assume You Were Thinking About Using VBA Within Access. If You're Feeling Adventurous, Try Visual Basic 2005 Express. Again It's Free & Downloadable From http://msdn.microsoft.com/vstudio/express/vb/

[matale]

I am trying to learn about databases and at the moment starting to read general stuff about multidimensional database design. The trouble is, I don't even know the basics about databases, like what is natural key (same as primary key made of single components?) or surrogate key... Could anyone explain the following paragraph (we are talking about star schema here):

- "Another important feature is that all natural keys are replaced with surrogate keys: this means that every join between the fact & dimension tables is based on surrogate keys, not each surrogate key should have a generalised structure. i.e. based on simple integers. The use of surrogate keys allows the data in the warehouse to have some independence from the data used and produced by OLTP systems. For example, each branch has a natural key, namely the branchID and surrogate key branchID."

I cannot see any difference between this surrogate key and foreign key in fact table....

and I had the impression that the data produced in any data model should be wholly independent from the programing, OLTP etc? So the line about surrogate keys not always based on simple entegers must give a clue?

I think you are reading the wrong stuff, star schemas and serrogate keys are used in Data Warehousing which is different from database design.
For your purposes I don't think that will be necessary. Just get yourself a good Access book or visit a few access tutorial websites.

[claptrap]

I know reading about multidimensional tables was a bit "jumping the gun, I just wanted to understand the principle...

Well, I have tried to design a simple table for vehicle hire company in Access 2000 (got a copy from a friend). It includes also a table for repairs that are done by garages. But I ran into problems when trying to link the tables together, with "enforce referential integrity" box ticked on.

Could you have a look? I've tried to upload the screendump of relationships into I think this better illustrates what my problem is, that is to say I've found one but don't understand it so don't know how to fix it. :(
http://www.freewebs.com/claptrpap/relationships.htm

I hope the explanation makes some sense, it seems that whateverI do, the site cuts a certain % out of images regardless of original size.

Also, for customer table, I want to create a list box, with a default value "Mr". I managed to create the box but when I choose one the values in the list (in table view) access complains about it not being valid. What do I do wrong? Also I want to set the default value to "Mr" but don't know how to do it. If I write "mr" into the Default line, Access won't recognise it as valid and won't save the table.

Until I have managed to get the design right I cannot enter any data and start creating queries. :(
I want this database up and running before I start my second job after next week as then I won't have any spare time.

[matale]

I think the error is because you are using different data types for the vehicleLicence on different tables, they are supposed to be the same.

Im not sure about the other stuff.

[claptrap]

Thank you matale! I did go through the data types again and found the non-matching errors as you suggested. I had simply followed the data dictionary I had created earlier, when I didn't quite understand about data types and foreign keys... :icon_redface: Basic database structure is now sorted and I can finally start inputting data. :)

Only now I want help with data validation: How do make sure a start date is earlier than end date, e.g. a kid starts at school when is 5 and copletes A-levels when he is 18 - not the other way round? You'd think it is a basic knowledge but I cannot find any examples from the books I found from my local library.

I have created a customer form with hire sub form: is there a simple way to retrieve details from an existing customer within the input form? I would also like a button on the form that once clicked, a label would appear e.g. in footer, to display a number of days (of hire/booking) and total price of hire.... Displaying price before and after VAT would be fantastic, I would never have to reach for calculator again: no typing mistakes, no embarrasing conversations with customers. :cool: But I understand that would be quite complicated. Even with ready made macro or code, you would have to tell me step by step where I should install it...

So far all I have come up with is a calcuation in query (the hire price is stored as a field with Goods table) First I calculated the number of days: NoDays: [HireEndDate]-[HireStartDate] and the next field I calculated the total hire price: HirePrice: [NoDays]*[DailyRate] in the field property query design - I couldn't combine the two expressions to come up with just total, but luckily in this case it didn't matter. :)

I'm still looking for a book from beginners to advanced Access 2000, although it seems that XP/2002 is very similar. I would have never thought databases are so much fun! The more I leran, the more uses I can think for my own purposes.