Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Question DB table design issue - Array

    In my project, there are quite few cases using array. I know a few options in regarding of implemention. Sub-table is oneof them. One situation is an array of primary key fields of one table, say friends( that is userid of account table). What a sub-table shall be desinged in this example?

    Table account (
    userid varch(20) primary key,
    streetaddress varch(80)
    )

    Table friend ( ... )

    Thanks for your advise.

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

    Re: DB table design issue - Array

    Originally posted by vwu98034
    In my project, there are quite few cases using array. I know a few options in regarding of implemention. Sub-table is oneof them. One situation is an array of primary key fields of one table, say friends( that is userid of account table). What a sub-table shall be desinged in this example?

    Table account (
    userid varch(20) primary key,
    streetaddress varch(80)
    )

    Table friend ( ... )

    Thanks for your advise.
    I don't really understand the question. What is table friend supposed to represent?

    If you are saying that a friend may have many accounts (an "array" of accounts if you like), then it would be:

    Table friend( friend_id primary key, ... )
    Table friend_account( friend_id /* FK to friend */,
    userid /* FK to account */ )

    Example data:

    ACCOUNT
    userid streetaddress
    123 xxxx
    234 yyyyy
    345 zzzz

    FRIEND
    friend_id
    Joe
    Jane

    FRIEND_ACCOUNT
    friend_id userid
    Joe 123
    Joe 234
    Jane 234
    Jane 345

    This records that Joe has accounts 123 & 234, and Jane has accounts 234 & 345. Each friend could have any number of accounts from 0 to infinity.

  3. #3
    Join Date
    Oct 2002
    Posts
    37

    Arrow

    The relation I try to represent in DB tables is the followings:

    user:

    user id (primary key)

    ...

    friend: (one user's friend, who are also users)

    userid (foreign key of user table)

    friend1 (foreign key of user table)

    friend2 (foreign key of user table)

    ...

    My question is how sub-table plays a role in this siutaion, or any other better method?

    Thanks,

    v.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I still don't understand. Maybe you could illustrate what you are trying to achieve with an example? I mean, if you are trying to store the many friends that a person has, e.g.

    Joe (user 123) has friends Bill (user 234), Alice (user 345) and Clare (user 456), then a suitable data model would be:

    Person( userid primary key, name )
    Friend( userid, friend_userid )

    Data:

    Person
    123,Joe
    234,Bill
    345,Alice
    456, Clare

    Friend
    123,234
    123,345
    123,456

    I would not use "subtables" (or "nested" tables) to do this.

  5. #5
    Join Date
    Oct 2002
    Posts
    37
    Thanks very much Tony.

    That is what I try to achieve. The approach is much better than what I use right now, csv, in terms of data consolidation. With foreigh key for both two columns in the friend table, the data in the table would be validate after one user, either in the first column or second column, is removed from the system.

    Another situation is what will be a good approach for ethnicity field of a user profile table. A person can have more than one ethnicities, mixed race. A solution I can think about is the "csv". What will be your suggestion?

    Thanks again for your helps.

    v.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    By "CSV" I guess you mean "comma separated values"?
    That is not the way to go in a relational database - it violates First Normal Form (no repeating groups).

    If a person can have 1, 2 or more races then again that is a one-to-many relationship best designed as a separate table:
    Person( ID, name )
    Person_Race( Person_ID, Race ) /* PK is (Person_ID, Race) */

    Sample Data:

    Person
    123,Joe
    234,Bill

    Person_Race
    123,Caucasian
    123,Asian
    234,Caucasian

  7. #7
    Join Date
    Oct 2002
    Posts
    37
    Is that type of table relationship called sub-table?

    table profile:
    userid
    username
    address
    ...
    age
    race
    ...

    table race:
    type (asian, caucasian ...)

    table user_race
    id
    userid foreign key of profile
    ethnicity foreign key of race

    How a select statement will be like for searching people between age 30 - 40 and race is caucasian?

    Does this approach will have a SQL performance issue for almost of people have single race case?

    Thanks for your helps.

    v.

  8. #8
    Join Date
    Oct 2002
    Posts
    37
    Also, for the friend table case, is it better to have update and delete on cascade. The friend table I am thinking about is like the following:

    Friend:
    id integer primary key,
    user varchar(20) references Person on update cascade on delete delete cascade,
    friend varchar(20) reference Person on update cascade on delete cascade




    ~~~~~~~~~~~~~~~~~~~~~
    Originally posted by andrewst
    I still don't understand. Maybe you could illustrate what you are trying to achieve with an example? I mean, if you are trying to store the many friends that a person has, e.g.

    Joe (user 123) has friends Bill (user 234), Alice (user 345) and Clare (user 456), then a suitable data model would be:

    Person( userid primary key, name )
    Friend( userid, friend_userid )

    Data:

    Person
    123,Joe
    234,Bill
    345,Alice
    456, Clare

    Friend
    123,234
    123,345
    123,456

    I would not use "subtables" (or "nested" tables) to do this.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by vwu98034
    Is that type of table relationship called sub-table?

    table profile:
    userid
    username
    address
    ...
    age
    race
    ...

    table race:
    type (asian, caucasian ...)

    table user_race
    id
    userid foreign key of profile
    ethnicity foreign key of race

    How a select statement will be like for searching people between age 30 - 40 and race is caucasian?

    Does this approach will have a SQL performance issue for almost of people have single race case?

    Thanks for your helps.

    v.
    It's not called a sub-table. It's sometimes called a Detail table (as in Master/Detail).

    A select statement would be something like:

    SELECT p.userid, p.username
    FROM profile p, user_race r
    WHERE p.age BETWEEN 30 AND 40
    AND r.userid = p.userid
    AND r.race = 'caucasian';

    I would not be too concerned about performance, as long as the tables have appropriate indexes. Joining tables together is what relational databases are good at!

  10. #10
    Join Date
    Oct 2002
    Posts
    37
    Thanks Tony again for your information.

    I would like to follow up this thread with the followings:

    1. A lot of joins are needed for detail tables if there are a lot of detail tables. I am not sure whether that cause any problems. I, however, do know that implemention will lead to a very long SQL statment. I have a case has more than ten detail tables if detail tables are used.

    2. The detail table design utilizes the DB strength to make search possible in the DB layout. I, however, think that is not only option for a list of data. For example,

    Car:
    color
    type

    PreferredCar:
    colors (one or more colors)
    type (one or more types)

    The PreferredCar is used as search elements against Car data. The PreferredCar doesn't needed to be stored in detail tables.

    3. Back to an early question, a multi-column key, I think, shall be for the Friend table. Is it a portable solution?

    4. In your first response, you mentioned relation DB normalization. Here is a case:

    SignOn:
    userid
    passwd

    Account:
    userid
    username
    address
    ...

    Profile
    userid
    BOD
    race
    ...

    From relationship DB point of view, all these three tables shall merge into one table. I have seen this DB table design as a blue print. From object-oriented design point of view, the approach makes more sense.

    Thank,

    v.

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    1. Joins are what relational databases are all about - don't be afraid of them! Yes, it makes SQL statements longer, but it gives you more flexibility in queries also.

    2. You mean something like:
    INSERT INTO PreferredCar( colors, type ) VALUES ( 'red,blue,green', 'saloon,coupe')

    Then you will need to parse the lists to compare with Car, e.g. SELECT Car.* FROM Car, PreferredCar PC WHERE PC.colors LIKE '%' || car.color || '%' AND PC.type LIKE '%' || car.type || '%'

    Where is the benefit in that over this?:
    SELECT Car.* FROM Car, PreferredColor PC, PreferredType PT WHERE PC.colors = car.color AND PT.type = car.type;

    No, I don't see that.

    3. You mean PK of Friend table is (UserID, FriendID), i.e. has 2 columns? No problem! If it was 4 or more columns, it would start to get inconvenient, and you might want to introduce a surrogate primary key (sequence number), but keep the multi-column key as a UNIQUE constraint.

    4. Normalisation does NOT say that you have to combine those 3 tables into one just because they have the same PK. It might make sense to do so, or it might not. If every user has Signon, Account and Profile information then a single table could be used, but doesn't have to be.
    If many users have no Profile, then a separate Profile table may be preferred (or not). A designer is free to decide which way to cut it.

    I suggest you get your hands on a good book about relational database design so you understand it better, then these matters may become clearer.

  12. #12
    Join Date
    Oct 2002
    Posts
    37
    1. I currently already join as many as four tables together without using detail tables. I am afraid of very long SQL statement difficult to maintain.

    2. One table vs. multiple tables

    I am not sure how that will/won't have impact on creation and deletion operations.

    3. I first designed the Friend table with a sequence number. My second thought is the sequence is not essential. I can live well without it since the two columns (user & friend) are both foreign keys and a combination is unique in the table. I do use a sequence number for a message table (sender, reciever, message), which a multi-column key is not a good option.

    Someone has recommended "Database Design For Mere Mortals". I will read through it once I get a copy of the book. I can't find it in a local book store. Most of DB books are not about DB table design.

    Most of my career time is spent on either software maintenance, or front-end development. This is my first doing back-end DB table design. Recently, I am recalling what I had learnt from the DB course in college some years ago while laying out table structure. I don't want to make any big DB table design errors. Otherwise, it will be a nightmare to migrate to new table structure once tables are full loaded with data.

    Thanks for your kindly helps.

    v.

  13. #13
    Join Date
    Oct 2002
    Posts
    37
    One more thought for the example

    "2. You mean something like:
    INSERT INTO PreferredCar( colors, type ) VALUES ( 'red,blue,green', 'saloon,coupe')

    Then you will need to parse the lists to compare with Car, e.g. SELECT Car.* FROM Car, PreferredCar PC WHERE PC.colors LIKE '%' || car.color || '%' AND PC.type LIKE '%' || car.type || '%'

    Where is the benefit in that over this?:
    SELECT Car.* FROM Car, PreferredColor PC, PreferredType PT WHERE PC.colors = car.color AND PT.type = car.type;

    No, I don't see that."

    Let's assume the PreferredCar is for one person and several car dealers offer various cars. I want to find out those car dealers who have cars met one person's car preference metric. How a query statemtn will play out? Is it something like the followings?

    SELECT Car.userid FROM Car WHERE EXIST (SELECT Car.* FROM Car, PreferredColor PC, PreferredType PT WHERE PC.colors = car.color AND PT.type = car.type AND PC.userid='Sam')

    Car:
    userid
    color
    type

    PreferredCar:
    userid
    colors
    types

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by vwu98034
    1. I currently already join as many as four tables together without using detail tables. I am afraid of very long SQL statement difficult to maintain.

    2. One table vs. multiple tables

    I am not sure how that will/won't have impact on creation and deletion operations.

    3. I first designed the Friend table with a sequence number. My second thought is the sequence is not essential. I can live well without it since the two columns (user & friend) are both foreign keys and a combination is unique in the table. I do use a sequence number for a message table (sender, reciever, message), which a multi-column key is not a good option.

    Someone has recommended "Database Design For Mere Mortals". I will read through it once I get a copy of the book. I can't find it in a local book store. Most of DB books are not about DB table design.

    Most of my career time is spent on either software maintenance, or front-end development. This is my first doing back-end DB table design. Recently, I am recalling what I had learnt from the DB course in college some years ago while laying out table structure. I don't want to make any big DB table design errors. Otherwise, it will be a nightmare to migrate to new table structure once tables are full loaded with data.

    Thanks for your kindly helps.

    v.
    1. Don't be afraid! Joins are OK. Denormalising data to reduce joins is a bad idea! You can use views to pre-join tables that are commonly queried together, if it helps.

    2. Normalised tables make creation and deletion easier. For example, to add another preferred color:
    INSERT INTO PreferredColor( Userid, Color ) VALUES (123, 'black');
    I would suggest that is at least as easy and efficient as:
    UPDATE PreferredCar SET Colors = Colors || ',black'
    WHERE UserID = 123;
    Then to remove red as a preferred color:
    DELETE FROM PreferredColor WHERE UserId=123 and Color = 'red'
    is definitely easier than:
    UPDATE PreferredCar SET Colors = {Substr Colors up to 'red'} || {Substr Colors after 'red'}

    3. That sounds right. If real data provides a good, reliable PK, there is no need to use a surrogate key. Surrogate keys are useful when (a) key consists of many columns so is cumbersome, (b) real keys are potentially subject to update (c) no natural key can be found - e.g. it is quite possible for 2 people to have same name, so a Person table typically uses a surrogate key.

    I have heard of the "Mere Mortals" book but have not read it. A good book for learning the fundamentals of relational database design is C J Date's "An Introduction to Database Systems". The author is one of the founders of the relational database.

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by vwu98034
    Let's assume the PreferredCar is for one person and several car dealers offer various cars. I want to find out those car dealers who have cars met one person's car preference metric. How a query statemtn will play out? Is it something like the followings?

    SELECT Car.userid FROM Car WHERE EXIST (SELECT Car.* FROM Car, PreferredColor PC, PreferredType PT WHERE PC.colors = car.color AND PT.type = car.type AND PC.userid='Sam')

    Car:
    userid
    color
    type

    PreferredCar:
    userid
    colors
    types
    Your query simplifies to:
    SELECT Car.* FROM Car, PreferredColor PC, PreferredType PT WHERE PC.colors = car.color AND PT.type = car.type AND PC.userid='Sam';

    I don't quite follow your tables, though. Where is the "dealer" in this query? I would expect to see something like:
    Dealer( dealer_id PK, dealer_name )
    Car( car_id PK, dealer_id, color, type )
    Customer( cust_id PK, cust_name )
    Customer_Color( cust_id, color ) /* PK is cust_id, color */
    Customer_type( cust_id, type ) /* PK is cust_id, type */

    Now to find out which cars match the customer's requirements, and which dealers have them:

    SELECT car.car_id, car.color, car.type, dealer.dealer_name
    FROM car, dealer, customer_color cc, customer_type ct, customer
    WHERE dealer.dealer_id = car.dealer_id
    AND car.color = cc.color
    AND cc.customer_id = customer.customer_id
    AND car.type = ct.type
    AND ct.customer_id = customer.customer_id
    AND customer.customer_name = 'Sam';

    Output might be:
    CAR_ID COLOR TYPE DEALER_NAME
    ---------- ---------- ---------- --------------------
    100 black saloon Dodgy Motors
    102 red saloon Dodgy Motors
    201 red saloon Slick Autos

    As you can see, I joined 5 tables there - no problem! Actually, I could have left out the Customer table if I just used the customer_id rather than the customer_name.

Posting Permissions

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