tekcap Posted March 4, 2007 Share Posted March 4, 2007 Hello Im creating a ERD for my Database and am having some problems. First of all my Database which is for an Mp3 shop has the following Entities so far. Customer , Administrator , Purchase , Cart , News , Currency , Label , Artist , Song , Genre , Review. My problem right now is if im keeping referential integrity inbetween my tables and I delete a song then im also deleting any Purchases in my Purchase Table that contain that song. This is a bad idea because I always have to keep track of sales even if I delete a particular song. What is a safe method for creating such a database. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 4, 2007 Share Posted March 4, 2007 Can't you say on delete do nothing? Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 4, 2007 Author Share Posted March 4, 2007 How would I do that? Im also wondering if I should create a column in user called Logged_in_status or if I should make a whole other table called Logged_in. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 5, 2007 Share Posted March 5, 2007 How would I do that? You'll have to check the InnoDB refman page... I don't use it, so I don't know, but I'm fairly sure I've seen this somewhere. Im also wondering if I should create a column in user called Logged_in_status or if I should make a whole other table called Logged_in. That depends how you're going to query it. Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 7, 2007 Author Share Posted March 7, 2007 here is a link to my ERD. http://img185.imageshack.us/my.php?image=erdao9.jpg Am I missing anything here? Should I be connecting these relationships any more? Im also wondering if I should put a on off status on tables such as song so if delete a song im not breaking referential integrity to l the purchase items. Is this a good way to approach this? Any links to tutorials that I might find appropriate would be appreciated as well. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 7, 2007 Share Posted March 7, 2007 Well, yes, you may need some sort of activity field here.... Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 8, 2007 Author Share Posted March 8, 2007 Ok so I will apply an activity field to the neccesary tables. Thanks ! I was also wondering how should I relate my review table to my song table? Also wondering if my users are choosing country and province does that mean I need tables for Countries and Provinces? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 8, 2007 Share Posted March 8, 2007 That depends on how you're going to query them... but you can probably get away with a denormalized table here. Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 8, 2007 Author Share Posted March 8, 2007 Ive never heard the term denormalized table what is that? I was thinking of also using a php include DEFINE file that lists all the Countries and the currencies associated with it. I also just thought of something. My artist and song relationship is a many to many because a song may have many artists and an artist may have many songs. Should I create a table called artist_songs? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 8, 2007 Share Posted March 8, 2007 Yes, artist_songs is a good idea... you need a lookup table for just about any M->N relationship. By denormalized, I simply mean that somewhere you'd have a table where multiple records could share the same field value multiple times (e.g. country for users). A normalized table would have a separate table for country, and store a country_uid. Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 9, 2007 Author Share Posted March 9, 2007 I was told to get rid of my admin table and just put access levels in the user table. Will this improve coding readability and speed up database access? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 9, 2007 Share Posted March 9, 2007 Admin table? Quote Link to comment Share on other sites More sharing options...
artacus Posted March 9, 2007 Share Posted March 9, 2007 A good rule of thumb when dealing with databases is delete nothing. You don't delete an mp3 instead have a status or active field to identify the "deleted" ones. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 9, 2007 Share Posted March 9, 2007 Oh, the hell of JOINs and indexing with activity fields... I feel you pain in advance. Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 9, 2007 Author Share Posted March 9, 2007 Fenway I have an administrator table which contains a username and password. Should I just get rid of the table and put access levels in the user table? If it will improve speed and make code easier to read ill change it. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 11, 2007 Share Posted March 11, 2007 You mean login/pass for each user? Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 11, 2007 Author Share Posted March 11, 2007 I mean granting users different access levels which wouls basically be 1 or 0. 0 for normal users and 1 for administrator. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 13, 2007 Share Posted March 13, 2007 If it's going to be that simple for a long time, then yes, just flag the users. Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 13, 2007 Author Share Posted March 13, 2007 http://img99.imageshack.us/my.php?image=erdxs7.jpg This is my new ERD let me know what you think Quote Link to comment Share on other sites More sharing options...
fenway Posted March 14, 2007 Share Posted March 14, 2007 Looks pretty good to me. Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 15, 2007 Author Share Posted March 15, 2007 Was looking at my tables today and was wondering if maybe I should remove the E-Mail attribute from Purchase since its already in the User Table. If I do this will I still be able to additionally display the E-mail everytime I query up a list of purchases for viewing? Im also having trouble figuring what would be the optimum data type for my Available status and access level attributes. Basically I only need ON/OFF and 1 for Admin and 0 for User. Also wondering how long I should make my file references. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 15, 2007 Share Posted March 15, 2007 If you can get to the user record, you can pull the e-mail from there. You can use an ENUM datatype for your attribute/status flags, as long as they're not going to change very often. File references should only include relative pathnames, you can have the full path name in your app. Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 15, 2007 Author Share Posted March 15, 2007 Gonna do a bit of research on the ENUM data type. My problem with E-Mail being foreign key is that if a user changes his Email then it will change in the purchase history. Is there a way to avoid that? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 15, 2007 Share Posted March 15, 2007 How will it change? Quote Link to comment Share on other sites More sharing options...
tekcap Posted March 15, 2007 Author Share Posted March 15, 2007 I figured the E-Mail will Change in every table that uses it as a foreign key. Basically the way I see it is if a user changes his E-Mail then it will change all records of his E-Mail in all the tables that use it as a foreign key. Does it only apply to new records added? Or does it do that AND go change all records in the database contaning the Old Email? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.