Results 1 to 11 of 11

Thread: Single Table?

  1. #1
    Join Date
    Sep 2003
    Posts
    11

    Single Table?

    Table question

    I wish to create a database to hold information on source code dependencies.

    FileName
    Computer primarily on
    Language
    ClassNames
    Path to one example
    Filenames it depends
    Other Dependencies - access to resources etc
    Description

    This seems to only require one table. How would I then go about implementing this? Can I relate the table to itself or what?

    The dependencies is what does it. It makes it seem that I need to join the table to itself.

    Thanks for any help.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Single Table?

    Sounds like the relationship to itself is many-to-many, i.e.
    a) A file may depend on many other files
    b) A file may be depended on by many other files

    If so, that requires a separate "intersection" table like:

    CREATE TABLE file_dependency
    ( dependant_filename REFERENCES file
    , used_filename REFERENCES file
    , CONSTRAINT fd_pk PRIMARY KEY(dependant_filename, used_filename)
    );

    You may be able to think of better column names.

  3. #3
    Join Date
    Sep 2003
    Posts
    11
    Ok, that has given me some hints although I am not entirely sure that I totally understand how the intersection table works.
    That plus I haven't done any database design in ages (and haven't really done any advanced) is making me confused.

    Table_Project
    [
    Project name (primary key)
    Filename used
    etc
    ]

    Table_Files
    [
    Filename (primary key)
    Filename used
    ]

    Table_Dependencies
    [
    dependent filename
    used filename
    ]

    So filename used in table_project is one to many with Filename in Table_files.
    Filename used in Table_files is one to many with used filename in Table_dependencies.
    dependent filename in Table_dependencies is one to many with Filename in Table_files.

    Or am I completely wrong?

    Additionally I have only just come across Constraint. What exactly doesd this allow you to do? It makes sure that these the foreign keys exist?

    Thanks.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Using "X ----< Y" to mean "each X may have many Y, and each Y may have only one X", the relationships are:

    Files.filename ----< Projects.filename_used
    Files.filename ----< Dependencies.dependent_filename
    Files.filename ----< Dependencies.used_filename

    (I don't prefix table names with "Table_", it's pointless).

    I do not know what Files.filename_used would be in your example, it seems redundant.

    So suppose in Files you have:

    FILES
    ====
    Filename
    ----------
    ABC.doc
    DEF.doc
    FGH.doc
    IJK.doc
    XYZ.doc

    Then in Projects you could have:

    PROJECTS
    ======
    Project_name Filename_used
    P1 ABC.doc
    P2 ABC.doc
    P3 XYZ.doc
    P4 ABC.doc

    And in Dependencies you could have:

    DEPENDENCIES
    ==========
    Dependent Used
    ABC.doc DEF.doc
    ABC.doc GHI.doc
    GHI.doc FGH.doc
    XYZ.doc FGH.doc

    This says that file ABC.doc depends on both DEF.doc and GHI.doc, and that GHI.doc and ZYZ.doc both depend on FGH.doc.

    NB If these filenames are long strings like "/usr/docs/stuff/abc_12345.doc" then you might want to introduce a short surrogate key like file_id NUMBER(5) for convenience.

    Constraints are rules that you declare to the database to ensure data integrity. Your tables already have (or require) some:

    1) Primary Keys on each table

    2) Foreign Keys: wherever filename appears in a table other than Files, it requires a foreign key constraint, to ensure that the only values allowed are those that exist in the Files table. It takes a form like:

    ALTER TABLE Projects ADD CONSTRAINT Project_File_FK FOREIGN KEY (filename_ued) REFERENCES Files;

    Another type of constraint is the CHECK constraint. This can test that values in columns conform to rules, e.g.:

    ALTER TABLE Projects ADD CONSTRAINT Project_chk1 CHECK (LENGTH(project_name) > 2);

  5. #5
    Join Date
    Sep 2003
    Posts
    11
    ALTER TABLE Projects ADD CONSTRAINT Project_File_FK FOREIGN KEY (filename_ued) REFERENCES Files;

    okay, almost there I think. Is the Project_File_FK a unique identifier? If so, and I have two foreign keys, do I need two of these identifiers for the table?

    Thanks.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Project_File_FK is a (unique) name for the constraint, yes.

    Not to be confused with a UNIQUE constraint, which I forgot to mention earlier. This is used when another column (or set of columns) apart from the Primary Key must also be unique. For example:

    ALTER TABLE Projects ADD CONSTRAINT Project_UK UNIQUE (filename_used);

    This constraint would prevent 2 different projects using the same filename, if that was a requirement. This is in addition to the fact that the Primary Key of Projects is project_name, which must also of course therefore be unique.

  7. #7
    Join Date
    Sep 2003
    Posts
    11
    Well, I wrote out my text file and it worked (almost) straight away.

    CREATE TABLE environment
    (
    native CHAR(30) NOT NULL,
    PRIMARY KEY (native)
    );

    CREATE TABLE language
    (
    lang CHAR(30) NOT NULL,
    PRIMARY KEY (lang)
    );

    CREATE TABLE file
    (
    filename CHAR (30) NOT NULL,
    PRIMARY KEY (filename),
    language CHAR(30),
    CONSTRAINT File_Lan_FK FOREIGN KEY (language) REFERENCES language(lang)
    );

    CREATE TABLE dependencies
    (
    dependent_filename CHAR (30),
    CONSTRAINT Dependencies_Dep_FK FOREIGN KEY (dependent_filename) REFERENCES file(filename),
    used_filename CHAR(30),
    CONSTRAINT Dependencies_Use_FK FOREIGN KEY (used_filename) REFERENCES file(filename),
    CONSTRAINT fd_pk PRIMARY KEY(dependent_filename, used_filename)
    );


    CREATE TABLE project
    (
    project_name CHAR(30) NOT NULL,
    PRIMARY KEY (project_name),
    filename_used CHAR(30),
    CONSTRAINT Project_File_FK FOREIGN KEY (filename_used) REFERENCES file(filename),
    runs_under CHAR(30),
    CONSTRAINT Project_Runs_FK FOREIGN KEY (runs_under) REFERENCES environment(native),
    description CHAR(255)
    );
    So thanks a lot for your help.

    The main thing I wish to know know is on the mysql page http://www.mysql.com/doc/en/InnoDB_f...nstraints.html it shows
    [CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
    REFERENCES table_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION
    | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION
    | RESTRICT}]
    I don't entirely understand the on update and on delete.

    Thanks.


    Edit: - it allows me to insert into table 'file' without having the language defined in the language table. Am I doing something wrong?
    Last edited by Pedant; 09-17-03 at 13:38.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    The ON DELETE and ON UPDATE rules specify what should happen if an attempt is made to delete or update the PK of the referenced table. The options are:

    CASCADE - delete/update the corresponding children
    SET NULL - set the FK column of the children to NULL
    RESTRICT - disallow the delete/update of the parent
    NO ACTION - ???

    I'm afraid I don't know what NO ACTION does really. It sounds like it means delete/update the parent and just leave the child as it was. But that sounds the same as having no foreign key at all!

    RESTRICT is the most usual setting, and is the default on Oracle (can't speak for mySQL). This means an attempt to delete a parent that has children will result in an error message like "Cannot delete parent row while children exist". That is the safest, it forces the user to go and delete the children first if he/she really means it. If in doubt, always use RESTRICT.

    CASCADE is good for child data that is logically merely "part of" the parent. For example, ORDER and ORDER_ITEM perhaps, but definitely not PRODUCT and ORDER_ITEM.

    I have never used SET NULL or NO ACTION. They sound kind of flaky!

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by Pedant
    Edit: - it allows me to insert into table 'file' without having the language defined in the language table. Am I doing something wrong?
    Your code looks OK. I have a suspicion that mySQL doesn't actually enforce its constraints! It's a bit of a toy database really, as far as I understand it (never used it).

    The foreign key should certainly stop you putting a value into file.language that isn't in the language table. You can put a NULL there, but not a non-null value.

    Maybe a mySQL expert can step in here...? (You may want to post a question in the relevant forum).

  10. #10
    Join Date
    Sep 2003
    Posts
    11
    Originally posted by andrewst
    Your code looks OK. I have a suspicion that mySQL doesn't actually enforce its constraints! It's a bit of a toy database really, as far as I understand it (never used it).

    The foreign key should certainly stop you putting a value into file.language that isn't in the language table. You can put a NULL there, but not a non-null value.

    Maybe a mySQL expert can step in here...? (You may want to post a question in the relevant forum).
    I think it might mention something along these lines in the comments of mysql

    http://www.mysql.com/doc/en/InnoDB_f...nstraints.html

    I have heard that mysql is not great, I downloaded mysql and postgresql at the same time, mysql onto my windows box and postgresql onto my linux box. I decided to start on mysql as I could then both interface it with Java and motivate myself to learn some vb to interface with it. I might try it on postgresql then.

    Thanks for your help.

  11. #11
    Join Date
    Sep 2003
    Posts
    11
    It seems I may be able to enforce but Ihave to set the table type

    "TYPE=INNODB;"
    and I have to create required indexes as well.



    On a side note, this forum seems a bit hefty on the bandwidth size, 100kb downloads when opening a new page?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •