Issues with table design

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 827 Views - Last Post: 24 May 2013 - 01:10 PM Rate Topic: -----

#1 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 115
  • View blog
  • Posts: 1,355
  • Joined: 14-January 10

Issues with table design

Posted 30 April 2013 - 04:42 PM

I'm working on an application that keeps track of the files a user has uploaded to a server. The image below shoes the database that I have created so far. Originally I had planned to keep track of who the file belongs to by adding a userID to the document table. Then yesterday I got to thinking well what if employee A uploads a file but they also want it to be accessible to employee B because they are working on a project together. This creates problems with the original idea for the userID. Then I thought about having a user table and keep track of each document in it that the user has uploaded. Both solutions are very undesirable because they create a lot of redundancy which I really don't want. The last solution I thought of was to create a set number of userID's for the documents table. That way the user can share the document with up to let's say 5 people. However, this isn't very dynamic and I would like to avoid because if this team has 5 people well next time they may be divided up into teams of 12.

Posted Image

Does anyone have any recommendations for what I might could do? I'm really not sure the best way to handle this situation.

Is This A Good Question/Topic? 0
  • +

Replies To: Issues with table design

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3481
  • View blog
  • Posts: 10,033
  • Joined: 08-June 10

Re: Issues with table design

Posted 30 April 2013 - 04:47 PM

can’t you make a link table? like
table Document_Access (
  documentID, -- FK Document
  userID,     -- FK
  PRIMARY KEY (documentID, userID)
)

This post has been edited by Dormilich: 30 April 2013 - 04:48 PM

Was This Post Helpful? 0
  • +
  • -

#3 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 115
  • View blog
  • Posts: 1,355
  • Joined: 14-January 10

Re: Issues with table design

Posted 30 April 2013 - 04:50 PM

I'm not familiar with link tables. I'll have to look into that.
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3481
  • View blog
  • Posts: 10,033
  • Joined: 08-June 10

Re: Issues with table design

Posted 30 April 2013 - 04:56 PM

essentially they’re tables to do a n:m relation.
SELECT
    * -- whatever data you need
FROM
    link_table
INNER JOIN
    data_table_1
    ON link_table.fk_field_1 = data_table_1.fk_field -- match on foreign key
    -- alternately, if both FK fields bear the same name
--  USING(fk_field)
INNER JOIN
    data_table_2
    ON link_table.fk_field_2 = data_table_2.fk_field -- match on foreign key


This post has been edited by Dormilich: 30 April 2013 - 05:01 PM

Was This Post Helpful? 0
  • +
  • -

#5 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 115
  • View blog
  • Posts: 1,355
  • Joined: 14-January 10

Re: Issues with table design

Posted 30 April 2013 - 05:22 PM

I'm trying to follow this the best I can. In your first post you had

table Document_Access (
  documentID, -- FK Document
  userID,     -- FK
  PRIMARY KEY (documentID, userID)
)



Does PRIMARY KEY (documentID, userID) set both of them to be primary keys?
Was This Post Helpful? 0
  • +
  • -

#6 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3481
  • View blog
  • Posts: 10,033
  • Joined: 08-June 10

Re: Issues with table design

Posted 30 April 2013 - 07:24 PM

no, that’s a compound key.
Was This Post Helpful? 0
  • +
  • -

#7 Luckless  Icon User is offline

  • </luck>
  • member icon

Reputation: 292
  • View blog
  • Posts: 1,146
  • Joined: 31-August 09

Re: Issues with table design

Posted 01 May 2013 - 08:25 AM

It's what's called a relationship table and that is definitely the way I would go. Basically verbs are relationship tables. Users HAVE a document. so you'd create a table with user id and document id as foreign keys
Was This Post Helpful? 0
  • +
  • -

#8 AndrewMack  Icon User is offline

  • New D.I.C Head

Reputation: 6
  • View blog
  • Posts: 40
  • Joined: 12-April 13

Re: Issues with table design

Posted 09 May 2013 - 11:49 AM

You could still have a User_ID column in the Document table to identify the original owner. But what they're saying is when that when the original User wants to share a document with another User all you do is create a new record in that "Link" (also referred to as "Relationship") table with the Document ID of the Document being shared and the User ID of the User it will now be shared with. To stop sharing it you simply delete the record in the Link/Relationship table. This doesn't delete the User record nor does it delete the Document - it only deletes that link between the 2 which you are using to identify which documents are available to which users.

I hope this helps to clarify what was already said above - sometimes stuff just needs to be explained a few different ways before we really grasp what's being said!
Was This Post Helpful? 1
  • +
  • -

#9 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Issues with table design

Posted 09 May 2013 - 03:05 PM

I've got a similar situation on my website, in that there are projects, project types, users, and project users. The way I have designed my tables is almost exactly as Dormilich has suggested. In your situation, and using your nomenclature, I would design the tables like so:

Document
    documentID (PK)
    documentName
    companyID (FK -> Company.companyID)
    typeID (FK -> DocumentType.typeID)
    fileName


DocumentType
    typeID (PK)
    typeName


User
    userID (PK)
    userName
    ...plus any other personal user information


Document_Users
    documentID (PK)
    userID (PK)
    isOwner {bit/boolean field}


Company
    companyID (PK)
    companyName
    companyAddress
    contactPerson
    contactNumber
    parentID (FK -> Company.companyID)


Note that the information regarding who owns the document is on the link table (aka bridging table, junction table, and a host of other names - see the Wikipedia article). This design allows for as many or as little users to own and/or access a document as you desire. In other words, it is extensible.

Another thing to note is that I have removed the Parent_Company table, and simply moved the parentID to the Company table. Unless you have companies with multiple parents, this is the ideal way to store the data, as the relationship between a company and it's parent belongs to the company itself - there is no need to split it out to another table. I have seen people split information to other tables in order to keep the table sizes low, as that is what they have learnt fro dinosaur lecturers at college/university. This is bad practice - if a table is large and you don't want the database to groan under the row sizes during a table scan, then index the table up. The only situation where an entity's information should be split over multiple tables and/or rows is where the row size begins to exceed the limit of a single row. For this you need hundreds upon hundreds of columns. [/rant]

In order to get your document user information from the database, you need to use JOINs (I've spoken to privately, so I know where he's going with this). Here are a couple of scenarios, and how you retrieve that data (note that I will use Microsoft parameter notation here, i.e. @documentID means a particular ID of a document):

All the users of a particular document
SELECT
  User.userID,
  User.userName,
  Document_User.isOwner
FROM User
INNER JOIN Document_User ON Document_User.userID = User.userID
WHERE Document_User.documentID = @documentID



All owners of a particular document
SELECT
  User.userID,
  User.userName
FROM User
INNER JOIN Document_User
  ON Document_User.userID = User.userID
  AND Document_User.isOwner = TRUE
WHERE Document_User.documentID = @documentID



All documents with owners
SELECT
  document.documentID,
  document.documentName
FROM Document
WHERE EXISTS (
  SELECT *
  FROM Document_User
  WHERE Document_User.documentID = document.documentID
  AND Document_User.isOwner = TRUE
)
-- To filter results you can use an INNER JOIN or a WHERE EXISTS filtering method.
-- I prefer WHERE EXISTS since it tends to be quicker during the execution planning
-- and it also ensures that only one row is returned per Document, circumventing the
-- need to use a SELECT DISTINCT in the opening line of the query



All documents, full information
-- This returns multiple rows if there are multiple Document_Users
-- Note that I have not used SELECT *, as this is bad practice...
-- only ever retrieve the rows you require, this cuts down bandwidth usage
SELECT
  document.documentID,
  document.documentName,
  document.fileName,
  Document_Type.typeName,
  Company.companyName,
  Company.companyAddress,
  Company.contactPerson,
  Company.contactNumber,
  User.userName,
  Document_User.isOwner
FROM Document
-- Using LEFT OUTER JOINs ensures that at least one row will return for each document.
-- If any following information doesn't exist (i.e. - a document doesn't have a company)
-- then the SELECT information (e.g. - Company.*) will return NULL.
LEFT OUTER JOIN Document_User ON Document_User.documentID = document.documentID
LEFT OUTER JOIN User ON User.userID = Document_User.userID
LEFT OUTER JOIN Company ON Company.companyID = document.companyID
LEFT OUTER JOIN Document_Type ON Document_Type.typeID = document.typeID


This post has been edited by e_i_pi: 09 May 2013 - 03:28 PM
Reason for edit:: Some code tags were missing

Was This Post Helpful? 2
  • +
  • -

#10 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 115
  • View blog
  • Posts: 1,355
  • Joined: 14-January 10

Re: Issues with table design

Posted 10 May 2013 - 05:10 PM

Alright, I just have 2 questions. With the removal of the Parent Company table, you made the Company table have a parentID and you made it have a foreign key to the companyID in the same table. Did I read that right?

Second, I tried searching for Microsoft parameter notation, but kept coming up with something else other than what you did. Could you provide me with either an explanation of it or a link to the documentation for it so I can read up on it?

This post has been edited by RandomlyKnighted: 10 May 2013 - 05:12 PM

Was This Post Helpful? 0
  • +
  • -

#11 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Issues with table design

Posted 10 May 2013 - 05:32 PM

View PostRandomlyKnighted, on 11 May 2013 - 11:10 AM, said:

Alright, I just have 2 questions. With the removal of the Parent Company table, you made the Company table have a parentID and you made it have a foreign key to the companyID in the same table. Did I read that right?

Yep, so the parentID refers to the companyID of the same table. Keep in mind that you will need to make parentID nullable for the purposes of root nodes. If you want to learn about working with hierarchical data, I wrote a tutorial here, though it only covers MSSQL.

Quote

Second, I tried searching for Microsoft parameter notation, but kept coming up with something else other than what you did. Could you provide me with either an explanation of it or a link to the documentation for it so I can read up on it?

In MSSQL, you declare variables (parameters) as @parameterName.
In MySQL, you declare regular variables as parameterName and session-wide variables as @parameterName.
In PHP PDO, you declare parameters as ? or :parameterName.
In PostgreSQL, you declare variables as parameterName.

So, when I said Microsoft parameter notation, I really meant Microsoft variable declaration, which is why you see queries with clauses like myTable.myColumn = @myParameter. You'll need to adjust the queries to match the syntax of whatever DB/DBA you are utilising.
Was This Post Helpful? 0
  • +
  • -

#12 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 115
  • View blog
  • Posts: 1,355
  • Joined: 14-January 10

Re: Issues with table design

Posted 13 May 2013 - 08:38 PM

When I go to create the user table using the query below I seem to get an issue with the table name.

CREATE TABLE User
(
	id INT PRIMARY KEY NOT NULL, 
	fName varchar(25) NOT NULL, 
	lName varchar(25) NOT NULL, 
	email varchar(40) NOT NULL,
	gender varchar(1),
	password varchar(12) NOT NULL,
)



The error I get is
Incorrect syntax near the keyword 'User'.


Does SQL Server just not like for you to use User has a table name or am I missing something else?
Was This Post Helpful? 0
  • +
  • -

#13 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3481
  • View blog
  • Posts: 10,033
  • Joined: 08-June 10

Re: Issues with table design

Posted 13 May 2013 - 10:32 PM

View PostRandomlyKnighted, on 14 May 2013 - 05:38 AM, said:

Does SQL Server just not like for you to use User has a table name or am I missing something else?

"USER" is a Reserved Keyword in SQL Server.
Was This Post Helpful? 0
  • +
  • -

#14 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 115
  • View blog
  • Posts: 1,355
  • Joined: 14-January 10

Re: Issues with table design

Posted 14 May 2013 - 04:57 PM

Ah that makes sense. I can't believe I didn't think about that. I believe I got everything set up except for the junction table. Would someone please look over this to make sure I'm creating the junction table correctly?

CREATE TABLE Document_Users
(
	documentID int NOT NULL REFERENCES Document(documentID),
	userID int NOT NULL REFERENCES Users(id),
	isOwner int NOT NULL,
	PRIMARY KEY(documentID, userID)
)


Was This Post Helpful? 0
  • +
  • -

#15 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Issues with table design

Posted 14 May 2013 - 10:45 PM

You can still have a table called user, you just need to delimit it, like this:
CREATE TABLE [User]


Note that reserved keywords that are used as column names also require delimiting.

This post has been edited by e_i_pi: 14 May 2013 - 10:46 PM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2