SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 26
  1. #1
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to best do this schema

    I'm using MySQL and I'm building a CMS for a company to admin their CD inventory. I want to build in the capability for a user to search by artist (easy), by CD(easier) and also to find all CD's with a particular song on it. There iwll be hundreds of CD's so I'm sure the schema I came to is not very scalable. I'm hoping you all can make some suggestions how to optimize this schema...particulary the gold_cd tabel which has fields for song1-15...argh. I know that's inefficient but not sure how I can do it better. Here' the mysql dump:
    Code:
    #
    # Table structure for table `gold_artist`
    #
    
    CREATE TABLE gold_artist (
      artistid int(12) NOT NULL default '0',
      aname varchar(25) NOT NULL default '',
      aweb varchar(63) NOT NULL default ''
    ) TYPE=MyISAM;
    # --------------------------------------------------------
    
    #
    # Table structure for table `gold_cd`
    #
    
    CREATE TABLE gold_cd (
      cdid int(12) NOT NULL default '0',
      cdimg varchar(63) NOT NULL default '',
      title varchar(100) NOT NULL default '',
      descrip varchar(250) NOT NULL default '',
      song1 varchar(150) NOT NULL default '',
      song2 varchar(150) NOT NULL default '',
      song3 varchar(150) NOT NULL default '',
      song4 varchar(150) NOT NULL default '',
      song5 varchar(150) NOT NULL default '',
      song6 varchar(150) NOT NULL default '',
      song7 varchar(150) NOT NULL default '',
      song8 varchar(150) NOT NULL default '',
      song9 varchar(150) NOT NULL default '',
      song10 varchar(150) NOT NULL default '',
      song11 varchar(150) NOT NULL default '',
      song12 varchar(150) NOT NULL default '',
      song13 varchar(150) NOT NULL default '',
      song14 varchar(150) NOT NULL default '',
      song15 varchar(150) NOT NULL default '',
      multi tinyint(1) NOT NULL default '0'
    ) TYPE=MyISAM;
    # --------------------------------------------------------
    
    #
    # Table structure for table `gold_label`
    #
    
    CREATE TABLE gold_label (
      labelid int(12) NOT NULL default '0',
      name varchar(100) NOT NULL default '',
      lweb varchar(63) NOT NULL default ''
    ) TYPE=MyISAM;
    Any insight?
    Aaron
    Aaron Brazell
    Technosailor



  2. #2
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sketch, why don't you just create a 'Song' table and a 'CDSong' table. You can then add all Songs to the 'Song' table and then the 'CDSong' table will be the lookup table for the CD/Song tables. This way any Song can be associated with any CD without replication of the Song data and CD's can contain differing numbers of Songs.


    CDSong
    -------
    CDID
    SongID
    TrackPosition

    Song
    ------
    SongID
    SongTitle
    SongLength

  3. #3
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    would I integrate CDSong with my existing gold_cd table (minus all the song fields)?

    Sketch
    Last edited by Sketch; Jul 15, 2002 at 05:46.
    Aaron Brazell
    Technosailor



  4. #4
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with shane, looks to me like that would be the best way

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    /me's eyes burst into flames after looking at the design
    My eyes! My precious eyes!!



    shane's design looks good enough to me.

    Just to pick a tiny nit, please don't write SQL like:
    int(12) NOT NULL default '0'

    INT datatypes do not take a parameter like (12), unless you use ZEROFILL to pad the integer columns with zeros.

    Further, do not enclose 0 in quotes, that's a character when you do that. And as far as the VARCHAR columns go, default '' is the same as NULL, so drop the default.

    e.g.
    Code:
    CREATE TABLE gold_artist(
      artistid INT NOT NULL,
      aname    VARCHAR( 25 ) NOT NULL,
    etc.

  6. #6
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I edited my post above. damn you guys are quick!

    MattR, those were phpMyAdmin dumps and you know how those are

    Sketch
    Aaron Brazell
    Technosailor



  7. #7
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MattR, I guess I need some advice then. I have always (in recent months anyways) used UNIX_TIMESTAMP() to create a unique id rather than defining a unique field. Because of that, I use int(12) although I think I can use int(10) safely. If this is not a good practice how do you recommend me doing this?

    Aaron
    Aaron Brazell
    Technosailor



  8. #8
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Sketch
    would I integrate CDSong with my existing gold_cd table (minus all the song fields)?
    Sketch
    You'll need to have 3 separate tables to deal with the CD Songs (gold_cd, cdsong and song). This way any Song can be related to any CD.

    The CDSong.CDID is the foreign key to gold_cd.cdid and the CDSong.SongID is the foreign key to Song.SongID.

    The combination of the CDSong.CDID and CDSongID would make up the primary key for the CDSong table (you might want to include the TrackPosition in the primary key as well).

    So, to get all the Songs for a particular CD you'd need to JOIN all 3 tables (to get the song details as well).

    This'd mean that the song1 - song15 fields wouldn't be required any more.

  9. #9
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay, cerebral overload.

    I have never joined more than 2 tables so this is going to take a minute or two....maybe more for me to fully comprehend how to execute that query...

    But I'm sure I'll get it...

    Thanks.
    Sketch
    Aaron Brazell
    Technosailor



  10. #10
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Sketch
    MattR, I guess I need some advice then. I have always (in recent months anyways) used UNIX_TIMESTAMP() to create a unique id rather than defining a unique field. Because of that, I use int(12) although I think I can use int(10) safely. If this is not a good practice how do you recommend me doing this?

    Aaron
    I personally recommend that you use MySQL's Auto-Increment fields as your primary if you just need a sequential ID. Anyway, you can still put your UNIX_TIMESTAMP() into a field declared without ZEROFILL. The number in parentheses doesn't effect what an Integer can store which is -2.48 billion to 2.48 billion or something like that. The number in parentheses determines how it is display...

    If the column was INT(10) and the value was 1 it would display like: 0000000001 as long as you kept the ZeroFill (MySQL doesn't). If the column was INT the same value would display as 1. MySQL of course requires another parameter to actually activate ZEROFILL.
    Wayne Luke
    ------------


  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)

    minutiae

    i hope you folks don't mind me making a few inconsequential comments at the tail end of this thread

    sketch, you definitely want 3 tables if "all CD's with a particular song on it" is a requirement

    matt, "default '' is the same as NULL" might be true in mysql(*), but it certainly is *not* true in any other database -- i know you know the difference, but beginners generally do not, so it's important to make a distinction between null and a zero-length string

    (*) actually i don't know if it's true in mysql or not, because it would never occur to me to suspect that it might be

    shane, CDsong has foreign keys to CD and Song, and those two together are an adequate primary key... you do *not* want to "include the TrackPosition in the primary key as well" because in normal situations, track position is dependent on which song it is, and it would be necessary to make it part of the PK only if a particular song appears on the same cd more than one -- i've got albums where a song is on there twice, but it's never *exactly* the same cut, it's always the "reprise" or "slow version" or something, so it isn't really the same song
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but I thought you HAD to define a length for int. As I understand it, you are saying that I can define a column as just int and so 1 will equal 1. If I defined the column as int(10) then 1 would equal 0000000001. Is this correct? Since I don't want all the 0's, then I'd like to do without zerofill. If the value was 1 I want it to be 1.

    I guess what I'm asking is:

    Can I really define a column as just int as opposed to int(10)?

    Sketch
    Aaron Brazell
    Technosailor



  13. #13
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: minutiae

    Originally posted by r937
    shane, CDsong has foreign keys to CD and Song, and those two together are an adequate primary key... you do *not* want to "include the TrackPosition in the primary key as well" because in normal situations, track position is dependent on which song it is, and it would be necessary to make it part of the PK only if a particular song appears on the same cd more than one -- i've got albums where a song is on there twice, but it's never *exactly* the same cut, it's always the "reprise" or "slow version" or something, so it isn't really the same song
    Good point. Although Sketch may want to include a Unique constraint on the CD/TrackPosition combo.

    Sketch, thinking about you may not have to JOIN all 3 tables as it depends on the data you want. If you wanted to display the CD data and the Songs data for that CD then personally I'd run two queries (A and C), although it could be done with 1 (B).

    Code:
    -- Query A : Get CD data for CD
    SELECT	CD.CDId,
    		CD.CDImg,
    		CD.Title,
    		CD.descrip,
    		CD.multi,
    FROM	gold_cd CD
    WHERE	CD.CDId = <CDID>
    
    
    -- Query B : Get CD data and all Songs for CD
    SELECT	CD.CDId,
    		CD.CDImg,
    		CD.Title,
    		CD.descrip,
    		CD.multi,
    		CDS.SongID,
    		CDS.TrackPosition
    		S.SongTitle,
    		S.SongLength
    FROM	gold_cd CD
    		LEFT OUTER JOIN CDSong	CDS	ON CD.CDId = CDS.CDId
    		LEFT OUTER JOIN Song	S	ON CDS.SongID = S.SongID
    WHERE	CD.CDId = <CDID>
    		
    	
    -- Query C : Get all Songs for CD
    SELECT	CDS.SongID,
    		CDS.TrackPosition
    		S.SongTitle,
    		S.SongLength
    FROM	CDSong INNER JOIN Song	S	ON CDS.SongID = S.SongID
    WHERE	CDS.CDId = <CDID>

  14. #14
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    wow.....pretty queries......

    Thanks, Shane.

    Sketch
    Aaron Brazell
    Technosailor



  15. #15
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sketch,

    This is why I dislike MySQL (again).

    It's kind of confusing, but the M parameter does NOT change the size or length of an INT. An INT is always 4 bytes, that is why you have SMALLINT etc.

    Code:
    create table test( col1 int(5) zerofill );
    
    mysql> insert into test values( 440 );
    
    mysql> select * from test;
    +-------+
    | col1  |
    +-------+
    | 00440 |
    +-------+
    1 row in set (0.00 sec)
    
    mysql> insert into test values( 99999999999 );
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from test;
    +------------+
    | col1       |
    +------------+
    |      00440 |
    | 4294967295 |
    +------------+
    2 rows in set (0.01 sec)
    http://www.mysql.com/doc/N/u/Numeric_types.html

    You should declare your columns with the ANSI standard data-type "INT" and not the MySQL extention "INT(M)" unless you plan on using ZEROFILL. Otherwise it sets up a bad habit and the code is not portable to other RDBMSs.

  16. #16
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I follow you, Matt, but when I go to define the table as just plain INT, it doesn't take it. It sets the table back to have a field length. Now perhaps this can be fixed through commandline, but since all I have isi phpmyadmin to work with now, I'm having problems. So is this another phpmyadmin fluke, or can I change from INT(12) to INT from the commandline?

    Also, the link you gave seems to indicate there is just a NUMERIC column type, but I don't see that listed in my options. I see Decimal and Float and all the INT's but NUMERIC is not listed. Any ideas on this?

    Sketch
    Aaron Brazell
    Technosailor



  17. #17
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Numeric is just a way of saying it is a number and not a string (e.g. 'string' types are VAR/CHAR, TEXT, etc.).

    I think MySQL internally tacks on the INT( M ) in case, for some reason, you dump the table and then add zerofill, it puts it at the default. Personally I think that is pretty dumb. If you do not set a M size then don't tack it on when you dump. If you do, then keep track of it. Just something to keep in mind if you're writing applications and have hard-coded SQL for create tables (say in the installation routine).

  18. #18
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that CREATE TABLE stuff with default '0' and INT(M) isn't phpMyAdmin's fault in this case. that's the output of MySQL's SHOW CREATE TABLE. i hate that. and it capitalizes some SQL like NOT NULL but doesn't with most things such as VARCHAR.


    Originally posted by MattR
    And as far as the VARCHAR columns go, default '' is the same as NULL, so drop the default.
    why do you say that '' is the same as NULL. you can have a NOT NULL DEFAULT '', but not a NOT NULL DEFAULT NULL.

    BTW, i've started adding DEFAULT 0 or DEFAULT '' on all columns because i found this in the MySQL manual, Things that must be done in the near future:

    Don't add automatic DEFAULT values to columns. Give an error when using an INSERT that doesn't contain a column that doesn't have a DEFAULT.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  19. #19
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The DEFAULT '' vs. NULL I thought was due to MySQL totally ignoring DEFAULT values anyway (e.g. what you found Dr.). NOT NULL char fields (in MySQL) will behave like this (I think, I hate MySQL ):
    Code:
    If SQL statement does not explicitly insert NULL:
     No default and not null, insert empty string or 0 (depending on type of column)
        default and not null, insert default
    
     No default and     null, insert empty string or 0
        default and     null, insert default
    
    If SQL statement explicitly inserts NULL, then I think it will use NULL on everything which does not have a default.  I think. :D
    It is confusing. Anyways, disregard that portion of my comment-- I still don't understand what it means

  20. #20
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    in MySQL, the default DEFAULT (if none is given during table creation) is NULL if the column can be NULL, else 0 or the empty string. this seems like how it would be in any DB?

    from the MySQL manual for CREATE TABLE:

    A DEFAULT value has to be a constant, it can not be a function or an expression. If no DEFAULT value is specified for a column, MySQL automatically assigns one. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type

    • For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT column, the default value is the next value in the sequence.
    • For date and time types other than TIMESTAMP, the default is the appropriate zero value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See section 6.2.2 Date and Time Types.
    • For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value (if you haven't explicitly specified another default value with the DEFAULT directive).

  21. #21
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    And the War of the Titans begins...
    Aaron Brazell
    Technosailor



  22. #22
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by DR_LaRRY_PEpPeR
    in MySQL, the default DEFAULT (if none is given during table creation) is NULL if the column can be NULL, else 0 or the empty string. this seems like how it would be in any DB?

    from the MySQL manual for CREATE TABLE:

    How'd you get that all pretty with the colors and such?

    Yes, for NULL-able columns it will insert null, so change:
    No default and null, insert empty string or 0
    to
    No default and null, insert NULL

    I think the part where MySQL deviates is if you have a NOT NULL column with NO default.. It will, if I recall correctly, insert 0 or empty-string depending on the column type. Traditionally this WOULD give an error in another RDBMS.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    this seems like how it would be in any DB?
    not always

    sometimes the default DEFAULT for character columns is spaces -- particularly when it's a fixed-length character column, which, when you think about it, cannot take an empty string

    matt, i really admire you for attempting to document that weird and wacky mysql behaviour -- eveybody knows it's not a "real" database ;o)

    in a recent O'Reilly article, Ten MySQL Best Practices, best practice #5 is Stick to ANSI SQL

    the only exception i'll make to this rule, and i'll do it gleefully, is the ability to insert multiple rows of values with only one call to the database, e.g.
    Code:
    insert into yourtable
     (col1, col2, col3)
    values 
     (val1, val2, val3)
    ,(val1, val2, val3)
    ,(val1, val2, val3)...
    which is not standard sql but shoulda been
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My suggestion to advisors is to split this thread and take all this very wholesome and healthy SQL bantering and make it it's own sticky thread....seems like it would make a great thread on the same level as PHP & MySQL Coding Tips By the Doc...

    ...that and it really is drifting farther away from my thread......course I got my answer, I think, but still...

    Sketch
    Aaron Brazell
    Technosailor



  25. #25
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR
    How'd you get that all pretty with the colors and such?
    manually with vB code. quote my post to see...

    I think the part where MySQL deviates is if you have a NOT NULL column with NO default.. It will, if I recall correctly, insert 0 or empty-string depending on the column type. Traditionally this WOULD give an error in another RDBMS.
    yes, that's correct. regarding the last sentence about giving an error, i guess why they're going to change MySQL to also give an error if you don't specify a column in an INSERT that doesn't have an explicit DEFAULT. and that's why i've started specifying DEFAULTs on all columns.

    although i'm not exactly sure what, or if, i should specify as the DEFAULT on AUTO_INCREMENT columns. you're supposed to INSERT NULL (not 0) to get it to increment, so i guess i should use

    INT UNSIGNED NOT NULL AUTO_INCREMENT DEFAULT NULL

    it just seems weird to say DEFAULT NULL on a NOT NULL column.


Bookmarks

Posting Permissions

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