Author Topic: Database Design Help  (Read 1196 times)

A.Russell

  • EA User
  • **
  • Posts: 28
  • Karma: +0/-0
  • I love YaBB 1G - SP1!
    • View Profile
Database Design Help
« on: April 19, 2007, 01:34:39 am »
I am new to database modeling and was hoping someone could help me.

I want to create a database that stores information about words, a little bit like a dictionary.

Each word (memberword) has a headword. e.g. is -> be (headword). Headwords can have multiple memberwords. e.g. be -> is, was...

Thge confusing part for me is how to have multiple memberwords. Here is what I have so far:



This doesn't reflect that a headword can have multiple memberwords. How is this done?


« Last Edit: April 19, 2007, 01:36:03 am by A.Russell »
----------------------
Software Consultant
Analyst Programmer/ PM

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 5907
  • Karma: +71/-80
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Database Design Help
« Reply #1 on: April 19, 2007, 05:27:32 am »
Hi A (since you don't sign) :),

In data modelling, relationships are inherently bi-directional.  Thus your first relationship (on the right of your diagram) does both jobs.  Each MemberWord has a HeadWord and Each HeadWord can have zero or more MemberWords.

If you change the Diagram Properties to show Information Engineering notation and set parameters to name only, your model will look more like your database.

HTH,
Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

A.Russell

  • EA User
  • **
  • Posts: 28
  • Karma: +0/-0
  • I love YaBB 1G - SP1!
    • View Profile
Re: Database Design Help
« Reply #2 on: April 19, 2007, 09:07:45 pm »
Thank you. I changed my model as suggested. Would this create the appropriate schema?:

----------------------
Software Consultant
Analyst Programmer/ PM

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 5907
  • Karma: +71/-80
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Database Design Help
« Reply #3 on: April 19, 2007, 09:53:01 pm »
A,

Why don't you try it and see...  ;)

(seriously though, seems to be OK...)

Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

A.Russell

  • EA User
  • **
  • Posts: 28
  • Karma: +0/-0
  • I love YaBB 1G - SP1!
    • View Profile
Re: Database Design Help
« Reply #4 on: April 19, 2007, 11:30:39 pm »
I have tried it, and I think it's okay. Lacking experience in this, though. How does this look:

Quote
DROP TABLE IF EXISTS HeadWords
;
CREATE TABLE HeadWords
(

HeadWord VARCHAR(32) NOT NULL,

MemberWord VARCHAR(32),

lBNC SMALLINT,

lGSL SMALLINT,

lAWL SMALLINT,

PRIMARY KEY (HeadWord)
)
;
LOAD DATA LOCAL INFILE 'C:\\DatabaseSchema\\data_HeadWords.txt' INTO TABLE HeadWords LINES TERMINATED BY '\r\n';

///data_HeadWords.txt'
play      plays      \N      \N      3
      playing      \N      5      2
poo      poos      3      \N      4


DROP TABLE IF EXISTS MemberWords
;
CREATE TABLE MemberWords
(

MemberWord VARCHAR(32) NOT NULL,

HeadWord VARCHAR(32),

PRIMARY KEY (MemberWord)
)
;
LOAD DATA LOCAL INFILE 'C:\\DatabaseSchema\\data_MemberWords.txt' INTO TABLE MemberWords LINES TERMINATED BY '\r\n';


///data_MemberWords.txt'
plays      play
playing      play
poos      poo



Quote
///Results

Headword table:

  HeadWord  MemberWord  lBNC  lGSL  lAWL  
      play plays NULL NULL 3
        playing NULL 5 2
      poo poos 3 NULL 4

Memberword table:

  MemberWord  HeadWord  
      plays play
      playing play
      poos poo


When inserting multiple memberwords into the HeadWords tables, is it correct that there is an empty space for the primary key?
« Last Edit: April 19, 2007, 11:33:36 pm by A.Russell »
----------------------
Software Consultant
Analyst Programmer/ PM

«Midnight»

  • EA Guru
  • *****
  • Posts: 5651
  • Karma: +0/-0
  • That nice Mister Grey
    • View Profile
Re: Database Design Help
« Reply #5 on: April 20, 2007, 01:39:39 am »
No, there can never be a missing primary key in any (well formed) relational table. If you mean a missing pointer to the HeadWord for the top level...

There are two schools of thought on this: null for the foreign key, or a foreign key pointing the record to itself (the entry is its own HeadWord).

Both have advantages and disadvantages. In many cases either will work well in practice, although depending on other factors in any specific case one works better than the other.

It is worth your taking more time than just reading a few answers. Take a trip down to the library or a bookstore where they don't mind your browsing for an hour or two and look through the literature on relational design. There is much that you can pick up quickly in this area, and it would be time well spent.

David
No, you can't have it!

A.Russell

  • EA User
  • **
  • Posts: 28
  • Karma: +0/-0
  • I love YaBB 1G - SP1!
    • View Profile
Re: Database Design Help
« Reply #6 on: April 20, 2007, 05:33:45 am »
I live in Japan. I ordered a book in English from Amazon, but it will take some time to arrive. In the meantime I've been scouring the web and asked on this notice board.

I tried:
play plays \N \N 3
play playing \N 5 2
poo poos 3 \N 4

Though that would be a duplicate pk entry, and it didn't work anyway. When attempting to load it into the database it simply skipped that line. I can't set it as a foreign key either.

If I were programming, I would do something like:

ptrToMemberwordTable pMemberWords[]


However, there aren't any arrays. I've searched for an example, but haven't managed to find one.

----------------------
Software Consultant
Analyst Programmer/ PM

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 5907
  • Karma: +71/-80
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Database Design Help
« Reply #7 on: April 20, 2007, 05:26:25 pm »
Quote
There are two schools of thought on this: null for the foreign key, or a foreign key pointing the record to itself (the entry is its own HeadWord).
Yes, there are two schools of thought on this...  Just as there are two schools of thought on the right of the ordinary citizen to bear anti-tank weapons and other armaments of (local) mass destruction.

Avoid NULL foreign key references like the plague...  They are a recipe for disaster.  If you are just starting out don't get into bad habits...  The self-referencing pointer definitively ONLY means one thing.  I am my ow top of the tree.  A NULL could mean I'm just a sloppy programmer.

Paolo
« Last Edit: April 20, 2007, 05:35:10 pm by PaoloFCantoni »
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 5907
  • Karma: +71/-80
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Database Design Help
« Reply #8 on: April 20, 2007, 05:34:05 pm »
Your model also appears to be slightly conceptually anomalous.

Taking David's point, if you meant for each HeadWord to have a possible Parenthood, then you should rename the MemberWord column in the HeadWord table to ParentHeadWord.  This makes things a lot clearer.  You'll also need a self-referencing relations (commonly known as a "head-scratcher")on the HeadWord table.

Now... If you did, in fact, mean that HeadWord has a different relationship to MemberWord than the one you already have listed, then it should remain as MemeberWord, and you should create an additional relationship from HeadWord to MemberWord.

Does that help?
Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

A.Russell

  • EA User
  • **
  • Posts: 28
  • Karma: +0/-0
  • I love YaBB 1G - SP1!
    • View Profile
Re: Database Design Help
« Reply #9 on: April 25, 2007, 03:03:20 am »
I can see why they call it a "head scratcher."

Basically, I am trying to achieve a "one-to-many" relationship with the HeadWords table entries being the "one", and the MemberWords table entries being the "many."

For another example, taken from http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/SQL_for_Beginners.asp


Quote
Name Table

NameID Surname FirstName MiddleName Male AddressId
1 Smith Andrew John true 1
2 Smithe Fred John true 2
3 Wright Anne   false 3
4 Jones Emily Anne false 1
5 Wright David Peter true 3


Address Table

AddressId Line1 Line2 City ZipCode
1 A Street   London  
2 A Road A Town Oxon  
3 A House Village Oxon OX1 3ED


What if someone had more than one address? How would that be expressed?:

1 Smith Andrew John true 1,2   ???

And how would that look modelled visually in EA?


In the case I am attempting with my dictionary, I have used the words themselves as their identifiers. Should I replace them with an integer ID? I think this is called an "artificial key."

The relationship would be "P Cardinality" since one headword can have one or more memberwords: Example:

Headword: Air

Memberwords:
Air
Airy
Airier
Airiest
Airiness
Airily
Airless
Airs
Aired
Airing
Airings
Midair

Most of the time, the data would be searched by MemberWord. The HeadWord contains most of the interesting data, like what level it appears in in different graded lists.
« Last Edit: April 25, 2007, 03:12:22 am by A.Russell »
----------------------
Software Consultant
Analyst Programmer/ PM

A.Russell

  • EA User
  • **
  • Posts: 28
  • Karma: +0/-0
  • I love YaBB 1G - SP1!
    • View Profile
Re: Database Design Help
« Reply #10 on: April 25, 2007, 05:17:33 am »
Okay, another attempt. Perhaps my confusion was with the primary key? I have now set both the primary and foreign key in the HeadWords table to MemberWords:



Alternatively, would it be better to set an integer ID to each table as index? I would only ever search by word, though.
----------------------
Software Consultant
Analyst Programmer/ PM