Would this db structure be a bad idea?

[Red Squirrel]

I'm designing a server control panel and right now my table structure is basically something like this:

users table:
userid
username
password

addon domains table:
addondomainid
owneruserid
domainname


And so on. Basically a table for each type of data, with the proper fields for that object. A very typical way of doing things.

I just had an idea, to treat everything as an "object". So a user is an object, an addon domain is an object, an alias is an object, a DNS zone is an object, and a DNS zone entry is an object etc... In lot of cases objects would contain other objects. For example a user would contain many addon domain objects, and these addon domain objects would contain alias objects and so on...

If I structure this well it will make coding easier as I'd have standardized ways to query these objects.

The DB structure would also change to something like this:

objects table:
objectID
parentobjectID
objecttype

Fields Table:
objectID
Fieldname
Fieldvalue


Basically rather then have specific tables with specific fields, it would be a very general set of tables that are "catch all" if you will and have an actual table for fields. In fact I think this may be better as it will allow to add "modules" without much modification to the DB structure. However it would involve larger queries. For example if I want to list all the domains owned by a certain user I need to do a join query to get all fields matching objects of type domain matching the ID of the user.

But should I really be concerned about the speed of my queries these days? Even a bunch of large queries, we're still talking less then 1 second here, and DBs are made for speed and made to be queried. I've always tried to minimize the amount of queries in my programs but I'm wondering if I may be concentrating too hard on that, when I could instead let that slide a tad while maintaining easier to deal with code.

Just wondering how most people approach these things. If it matters, my program is written in C++. I'm only really working at the structure level now, so I want to build a solid foundation and make it super easy to add new modules and if I use my proposed system I will actually be able to add new modules without even modifying the DB, which I think is pretty cool.

 

[PhatoseAlpha]

Seems like a bad idea. As noted, you'll lose speed - but much, much more importantly you'll lose use of the DB's integrity controls - foreign keys, required fields, and the like. The textbook database structures serves partly to prevent unnecessary duplication, but it also serves to prevent orphans and bad data.

In the above example, the addon domains table would almost certainly have a foreign key constraint on it to prevent add-on domains from getting put into the table pointing to no owneruserid, or worse, to invalid owneruserids.

Trading a good bit of speed for flexibility may be acceptable, depending on the end users. Trading data integrity controls for flexibility is just a sure fire way to end up with a database loaded with bad data.

 

[WannaFly]

[Q]I will actually be able to add new modules without even modifying the DB, which I think is pretty cool.[/Q]

You may think its cool, but soon after writing a few queries, you'll find its a pain in the a**.

I can't find the article, but i've read one about bad database design that said why NOT to do this.

Queries would be horrible, given they'll still run relatively fast, but coding/maintaining them would be a nightmare.

instead of:
select addon_domains.* from addon_domains, users where
addon_domains.owneruserid=users.userid

you'd have to do:
select F1.FieldValue as 'RegisteredOn', F2.FieldValue as 'DomainName', F3.FieldValue as 'Prefix', F4.FeildValue as 'Owner' from objects, fields F1, Fields F2, Fields F3, Fields F4
where F1.objectID = objects.objectID and F1.FieldName='RegisteredOn'
and F2.objectID = objects.objectID and F2.FieldName='DomainName'
and F3.objectID = objects.objectID and F3.FieldName='Prefix'
and F4.objectID = objects.objectID and F3.FieldName='Owner'

 

[Red Squirrel]

[Q]I will actually be able to add new modules without even modifying the DB, which I think is pretty cool.[/Q]

You may think its cool, but soon after writing a few queries, you'll find its a pain in the a**.

I can't find the article, but i've read one about bad database design that said why NOT to do this.

Queries would be horrible, given they'll still run relatively fast, but coding/maintaining them would be a nightmare.

instead of:
select addon_domains.* from addon_domains, users where
addon_domains.owneruserid=users.userid

you'd have to do:
select F1.FieldValue as 'RegisteredOn', F2.FieldValue as 'DomainName', F3.FieldValue as 'Prefix', F4.FeildValue as 'Owner' from objects, fields F1, Fields F2, Fields F3, Fields F4
where F1.objectID = objects.objectID and F1.FieldName='RegisteredOn'
and F2.objectID = objects.objectID and F2.FieldName='DomainName'
and F3.objectID = objects.objectID and F3.FieldName='Prefix'
and F4.objectID = objects.objectID and F3.FieldName='Owner'

Yeah I was thinking that too. The idea though is that the base "Object" class would auto generate the queries as they'd be standard. So from the "front end" code I would never actually deal with them. I'm still debating on if I restructure everything to use this system or similar, maybe have a base object class, then have a table for each type instead of for fields. That way it would be a bit less of a hassle, just a join.

 

[MrChad]

I've worked with a few enterprise software vendors that use that type of schema in their products. The OBJECTS and FIELDS tables are generally accompanied by metadata tables that describe the types of data contained in the data tables.

Although it violates all sorts of conventional DB wisdom, it does offer you a lot of flexibility, especially for a highly customizable enterprise application, where users might want to create custom objects that can be searched/manipulated by the app. You will probably want to create a data access layer that abstracts the querying / inserting of records so that calling applications do not have to generate the SQL for these tables directly.

Unless you need a large degree of customization and flexibility though, I would recommend going with a more traditional schema design for your own sanity.

 

[Gunslinger08]

I believe SharePoint uses a similar DB schema for storing lists. I generally prefer to keep stuff strongly typed - in both code and storage. It does make it considerably easier to add more "objects" to the DB, but it also makes your queries take longer and much harder to read. If you're optimizing it for performance, you'll have a much easier time setting up indexes on strongly typed tables based on your most common queries.

 

[Markbnj]

That kind of typeless structure is much more difficult to comprehend, debug, and as others have pointed out, query and ensure integrity on. I would only go that route if the required functionality mandated it, meaning that the entity type system is not fully defined at build time.

 

[Ka0t1x]

At first I cringed, but I could also see this working... if you have some data layer abstraction doing the work for the application. And of course as Mark just said, I would really only use it if truly necessary.

 

[Red Squirrel]

At first I cringed, but I could also see this working... if you have some data layer abstraction doing the work for the application. And of course as Mark just said, I would really only use it if truly necessary.

Yeah there would be a layer that takes care of everything, for sure. Basically within the actual program code I would barely ever do any queries. I want to wrap all db access in ways that I'm never really accessing it directly.

What I have in mind is the "objects" would have a field for what server they belong to. In a clustered environment other server's objects would replicate to a designated slave server(s), upon downtime they would all become "active" on the slave server.

What I'm thinking is still have a table per object with predefined fields but have a "base" object table too. So it would simplify querying but still give me what I want to accomplish to some extent.

 

[degibson]

Don't store passwords. Store hashes of passwords instead.

 

[Red Squirrel]

Don't store passwords. Store hashes of passwords instead.

Yes of course. For now I'm actually just storing the passwords (I have a hash function that does nothing atm) but before I even release an alpha version I want MD5 or some other kind of hash. Just did not get around to figuring out the algorithm yet.

 

[FP]

... and they are a horrible idea in 99% of RDB design cases.

Trust me. Don't do it.

They really only make sense when you can guarantee you won't need to identify objects by more than one attribute. For example, how would you write a query to find all Animal objects with an attribute 'Color' of 'Red' AND a size of greater than 15Kg?

More info can be found here:

http://en.wikipedia.org/wiki/Entity-attribute-value_model
http://tkyte.blogspot.com/2009/01/this-should-be-fun-to-watch.html

Jump down to "When it's not right" in this post:
http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/

You are a far better using a regular data model.

 

[Oyster]

Don't do it. Been there done that. Was on the .NET Reflection wagon for sometime, and took that concept to the DB level. Initially, everything worked fine, but the performance hit was atrocious. As the application kept expanding, more and more issues became evident. The level of abstraction made quick querying virtually impossible. We decided to dump the application free of charge as a goodwill gesture for the customer, who was utterly pissed.

 

[Hmongkeysauce]

You may want to take a look at the ASP.NET Membership DB Scheme and Wordpress DB Scheme. If I can remember correctly, I believe those schemes use a similar design to what you are proposing.