Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database schema question

    Hi guys

    Just starting out on the database design process, and I thought I would post on here so you can verify my thinking.

    I,ve been designing a customer database, with the schema below file attached.

    I,ll post a second message with the question, as it requires 2 files.

    L8r
    Attached Thumbnails Attached Thumbnails Database schema question-database1.gif  

  • #2
    New to the CF scene
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here,s the second file.

    My question is this....

    Because not all customers will require an address, and those that do can only have one, i've change the pk in addresses to customer_id and changed the table name to customer addresses.

    But I want to keep my fk filed in the address_lines table as address_id, am I correct in thinking I can do this so long as my address_id field in customer_addresses remains an auto increment field?

    Also can anyone spot any flaws in the overall design?

    Any thoughts, tips, suggestions would be most welcome

    Thx guys
    Attached Thumbnails Attached Thumbnails Database schema question-database2.gif  

  • #3
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    I would get rid of the customer types table and just add type to the customer table since you only have one attribute for it. Normalization is good to reduce data redundancy however but on the other hand too much normalization can also reduce overall system performance.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #4
    New to the CF scene
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    Thx for the reply.

    I think I will do that, as it will only have 3 possible values and what goes in that field is dependant on which webpage the account is created from, and can be controlled by PHP.

    My second question regards mySQL Primary Keys and is this...

    Do primary keys have to auto-increment? is it not possible to insert a primary key value from a primary key from another table.
    I know you would not normally do this, as it describes a one-one relationship, but as you can see some customers dont have an address at all.

    Or is the address_id required?

    Thx again
    Attached Thumbnails Attached Thumbnails Database schema question-database3.gif  

  • #5
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    The Primary key does not need to be an auto-increment field but it must contain unique values which is why auto-increment is often used. If you are not going to use auto-increment then using some kind of number that will be unique for a particular person such as the persons ID number, drivers license number, etc.


    If a person does not have an address then there is no point in creating a record for them in the address table.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #6
    New to the CF scene
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi again

    Yes your right, maybe I didnt explain.

    In the 3rd schema for the addresses table, i,m using customer_id as the primary key, which is in turn the primary key from the customers table (a not null, auto increment value).

    Therefore if a customer doesnt require an address then no entries will be input into the address tabel.

    I just needed to verify that this was possible.

    Thx very much.

  • #7
    New Coder
    Join Date
    Aug 2002
    Location
    Cape Town, South Africa
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    This type of situation arises often in relational database design. What you are describing is called a foreign key. Foreign - because it is a primary key of one table that you include as an attribute in a second table.

    Most database systems allow you to specify foreign keys when you create the tables (much the same as you would declare a primary key for a particular table). The advantage of doing this, is that the system will ensure that any key inserted as a foreign key is in fact a valid attribute value (i.e it does appear as a primary key in the table that you are referencing). In other words, it will not allow you to put a customer_id, (say 007), as a foreign key if there is no such customer_id (007) appearing as a primary key.

    I am not sure of the sql to declare foreign keys in mysql (it should be in the manual) but it should not be too dissimiliar to the way you delcare primary keys ( try something like... foreign key(customer_id) referencing [name_of_table_that_contains_customer_id_as_a_primary_key] or something similiar)

    Hope this helps

  • #8
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    mysql does not support foreign keys. Although mysql is considered a relational database system it is not a true RDBMS though. To ensure data integrity all cascading of data insertion or deletion or updating must be hardcoded with whatever server side language you are using.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #9
    New Coder
    Join Date
    Aug 2002
    Location
    Cape Town, South Africa
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    ooops ... sorry about that! Thanks for clarifying Spookster. I learn something new everyday.... that'll teach me to reply to something I haven't actually tried myself!

    cheers


  •  

    Posting Permissions

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