Question Relationship Advice

[jscrinc]

Ok, I'm just doing a database layout in access, before transferring to MySQL, to help me understand how I'm going to make a nice login system.

So I have so far created two tables, A user table and a blog table.

The user table will hold User Details and the blog table will hold there blog input.
The field's in User includes:
(p)Username -> Text
Password ->Text
E-mail -> Text
Name -> Text
Last Name ->Text

The field's in Blog include
(p)Blog ID -> Autonumber
Blog Post -> Memo
Username ->Text

(p means primary key)

So I want to link User to blog as a one to many relationship. But All i'm getting is a one to one. The user shouldn't have one blog post but many, if you know what i mean.
Please help

 

[Rabbie]

Have you tried building the relationship using the Relationship screen? There shouldn't be a problem setting up a One to Many relationship between User and Blog. Post a copy of your DB so we can give you detailed advice

 

[jscrinc]

Have you tried building the relationship using the Relationship screen? There shouldn't be a problem setting up a One to Many relationship between User and Blog. Post a copy of your DB so we can give you detailed advice

Hi Here my Database

 

[Rabbie]

In table use_blog you have defined the field username as indexed-no duplicates. This means you can't use it for 1tomany relationships until you allow duplicates.

 

[jscrinc]

In table use_blog you have defined the field username as indexed-no duplicates. This means you can't use it for 1tomany relationships until you allow duplicates.

Oh.. So thats why, wonder why my database tutor never told me this

 

[Rabbie]

Oh.. So thats why, wonder why my database tutor never rold me this

AS a rule of thumb Primary keys No duplicates. Foreign Key Duplicates allowed.

 

[NotSoRandomOne]

...The field's in Blog include
...
Username ->Text

Why don't you simply store the user's autonumber ID as a field in the blog entry? This allows you to look up their name, and, if you need to, change it later, without having to update bad text fields all over the place. It also keeps your database much smaller, and, believe it or not, simplifies your life considerably. DLookups are much easier to do than the logic to change everything in incorrect duplicate fields.

I don't have 2007, or I would have made your db into a quick example.

 

[georgedwilkinson]

Gotta go with the NotSoRandomOne on this. All kinds of unforeseen things happen to text fields and they should not be used for foreign keys.

 

[NotSoRandomOne]

Also, I recommend to store first name, middle name, and last name in individual fields, so you can sort them in any way you desire. If you want to display the full name of the people, create a query that contains the ID of the person, as well as a field something like this:

PatientName: [LastName] & ", " & [FirstName] & " " & [MiddleName]

Then to get the name you want, you can do something like

DLookup("PatientName", "PatientNamesQuery", "ID = " & currentRecordSet![PatientNumber])

(I think that is correct on the second code, but I didn't cut and paste it like the first one was--the second one is from memory.) You can also use that query in reports. Note that the way it is coded, there is an extra space after the FirstName if there isn't anything in the MiddleName field. It could be done away with with more coding, but I haven't felt that it is worth the bother.

And for your future learning, something I came across this week is that if you use scratch tables to create reports from, always store the data in the scratch tables just as it is stored in the original tables that they are based upon. (And try to use _numbers_ for everything, as they are quick to copy around!) Then create a query that looks up the appropriate items after the table is populated, and base reports off of that query. My original design used DLookups to place the names and other stuff into the scratch table as strings, because I thought, "Cool, I can pull the report from that, and I won't have to do any more work!" Unfortunately, it took over five times longer to fill the scratch table and run the report than it did when I figured out this tip.

Hope this helps.

 

[jscrinc]

Why don't you simply store the user's autonumber ID as a field in the blog entry? This allows you to look up their name, and, if you need to, change it later, without having to update bad text fields all over the place. It also keeps your database much smaller, and, believe it or not, simplifies your life considerably. DLookups are much easier to do than the logic to change everything in incorrect duplicate fields.

I don't have 2007, or I would have made your db into a quick example.

I don't have have a user number ID, just username which is set to the primary key
But this lookup seems a lot better, again, another thing my Database tutor isnt teaching me, and I'm doing A level

 

[NotSoRandomOne]

Here is a quick example, if you can open 2003 files in 2007. Do the blog entry using the blog form, *not* the table, because the table will accept any number for the user input--even numbers that aren't assigned to users!

I could have made the user combo box display only the user name, but I thought that by leaving both columns visible, you might get an idea of what is going on behind the scenes. If you perform some extra work and make it so that only the name is displayed, the combo will become an autocomplete box, so when the user types 's' 'Smith, Mary' automatically selects, and the appropriate id number is saved in the table.

There is also a quick user report based off of the query. I hope it is enough to get you started.

As per your tutor, perhaps these topics were to be covered in the future. Understanding them was one of the hardest two weeks (not days) in my own journey through database land, so maybe they are still laying the groundwork.

Enjoy your journey!

 

[wiklendt]

I don't have have a user number ID, just username which is set to the primary key
But this lookup seems a lot better, again, another thing my Database tutor isnt teaching me, and I'm doing A level

from what i can tell just from these fora, is that people who teach databases in schools don't really teach anything about databases... ho hum. and your tutor might be a free-lance self-learner (or school-taught databaser) that hasn't got it quite right.

i've seen teachers posting questions about how to make a database work and they often have the MOST WRONG table structures i've seen in my life (if you exclude some of my earlier database attempts *blush*) but insist on keeping it the way they have it rather than take on the good advice by the wonderful people on this forum.

yes, it's really frustrating. but this forum will set you right it's where i learnt all i know about access (with a little extra-curricular googling mixed in)

then again, NotSoRandomOne has a point - some concepts are hard to embed... (edit: if you are doing A-level (i assume that's the basic level?) then you should have been taught the basics... like using autonumber primary keys - if you are paying this tutor, i suggest you find a well-educated one instead, or learn from the forum and other self-found resources, like many of the users here - it's best to learn the good way first time around than to un-learn bad habits)