If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

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?

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?

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.

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.

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.

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.

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?

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.

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?